SQL Cast Function Syntax Error

Question

Can anyone guide me in the right direction with this? I've tried editing my syntax every way possible and searched for 2 hours how to make this work. But I cannot get my CAST function to work properly. What am I doing wrong?

SELECT     dbo_orders.ordernumber, 
       dbo_orders.orderdate, 
       Sum([dbo_Order Details].priceperunit) AS sumofpriceperunit, 
       dbo_orders.producttotal, 
       Sum([dbo_Order Details].costperunit) AS sumofcostperunit, 
       dbo_orders.shippingtotal, 
       dbo_tracking.cost, 
       [dbo_Order Details].dropship, 
       dbo_orders.cartid 
CAST       ([dbo_Tracking].cost) AS number 
FROM       dbo_tracking 
INNER JOIN ([dbo_Order Details] 
INNER JOIN dbo_orders 
ON         [dbo_Order Details].ordernumber = dbo_orders.ordernumber) 
ON         dbo_tracking.numerickey = [dbo_Order Details].ordernumber 
GROUP BY   dbo_orders.ordernumber, 
       dbo_orders.orderdate, 
       dbo_orders.producttotal, 
       dbo_orders.shippingtotal, 
       dbo_tracking.cost, 
       [dbo_Order Details].dropship, 
       dbo_orders.cartid 
HAVING     ((( 
                                        dbo_orders.orderdate)>=(#6/1/2016#) 
                  AND        ( 
                                        dbo_orders.orderdate)<=(#6/30/2016#)));

RoMEoMusTDiE · Accepted Answer · 2017-04-12 03:15:51Z

msaccess cast functions

Cint -- cast to integer
Clng -- long
Cdbl -- double
Csng - single
Cstr - string
Cbool - boolean

SELECT clng(fieldName) FROM tableName

https://www.techonthenet.com/access/functions/datatype/cint.php

added ',' , cast the field, remove having clause and added where clause. you might want to format the date accordingly.

    SELECT     dbo_orders.ordernumber, 
           dbo_orders.orderdate, 
           Sum([dbo_Order Details].priceperunit) AS sumofpriceperunit, 
           dbo_orders.producttotal, 
           Sum([dbo_Order Details].costperunit) AS sumofcostperunit, 
           dbo_orders.shippingtotal, 
           dbo_tracking.cost, 
           [dbo_Order Details].dropship, 
           dbo_orders.cartid ,    'added a comma
    CInt       ([dbo_Tracking].cost) AS number  'cast accordingly
    FROM       dbo_tracking 
    INNER JOIN ([dbo_Order Details] 
    INNER JOIN dbo_orders 
    ON         [dbo_Order Details].ordernumber = dbo_orders.ordernumber) 
    ON         dbo_tracking.numerickey = [dbo_Order Details].ordernumber 

where   'added where clause
         dbo_orders.orderdate>=   (#6/1/2016#) 
   AND   dbo_orders.orderdate <=(#6/30/2016#)

    GROUP BY   dbo_orders.ordernumber, 
           dbo_orders.orderdate, 
           dbo_orders.producttotal, 
           dbo_orders.shippingtotal, 
           dbo_tracking.cost, 
           [dbo_Order Details].dropship, 
           dbo_orders.cartid 
 'removed having clause

Gordon Linoff · Accepted Answer · 2017-04-12 02:40:56Z

0

Normally, in MS Access you use one of the type conversion functions:

Cdec([dbo_Tracking].cost) AS number

If you don't want a decimal, check the documentation for the type you do want.

BTW, the correct syntax for cast includes a type. For example:

cast([dbo_Tracking].cost as decimal(20, 4))

answered Apr 12, 2017 at 2:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

DRK Over a year ago

Thanks for your help. For some reason it's still giving me Syntax Error (missing operator).

RoMEoMusTDiE Over a year ago

remove having and chuck a where clause before group by and count your parenthesis

Buddy · Accepted Answer · 2017-04-12 05:31:49Z

0

You are missing a comma next to dbo_orders.cartid

edited Apr 12, 2017 at 5:31

Buddy

11k5 gold badges47 silver badges63 bronze badges

answered Apr 12, 2017 at 5:29

Kapil

1,0876 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Cast Function Syntax Error

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related