Re: Need help w/ the DATE syntax

[alicejwz]

Re: Need help w/ the DATE syntax

Hi,

I'm trying to set the date criteria in my query but I'm trouble w/ the syntax. Could someone take a quick look? I'm using AC2K, SQL Server 2K.

This is what I tried:
str = "Select * from dbo.v_print_shipping_sched where [cust_ord_due]= #" & Date & "#"
Incorrect syntax near ‘#’.

str = "Select * from dbo.v_print_shipping_sched where [cust_ord_due]= # " & Date & " #"
Incorrect syntax near ‘3’.

str = "Select * from dbo.v_print_shipping_sched where [cust_ord_due]= " & Date & ""
Error display on report

Thanks much!

 

[Kodo]

Hi,

I'm trying to set the date criteria in my query but I'm trouble w/ the syntax. Could someone take a quick look? I'm using AC2K, SQL Server 2K.

This is what I tried:
str = "Select * from dbo.v_print_shipping_sched where [cust_ord_due]= #" & Date & "#"
Incorrect syntax near ‘#’.

str = "Select * from dbo.v_print_shipping_sched where [cust_ord_due]= # " & Date & " #"
Incorrect syntax near ‘3’.

str = "Select * from dbo.v_print_shipping_sched where [cust_ord_due]= " & Date & ""
Error display on report

Thanks much!

#Date# is only acceptable in Access. "Date" will throw an error becuase that denotes a number not a date (which can be considered a string).

'"&Date&"' is the proper syntax for sql server.

 

[alicejwz]

Re: Need help w/ Date syntax

Thanks for your reply, Kodo.
What should my query look like?

I tried below and it 's giving an error and no records on the report.
str = "Select * from dbo.tblshipping_sched "
str = str & "WHERE dbo.tblshipping_sched.cust_ord_due = '" & Date & "'"

Thanks!

 

[alicejwz]

Re: Need help in Date syntax

Hi again.
This is what I tried:
doesn't work: no records returned
'str = str & "WHERE dbo.tblshipping_sched.cust_ord_due = '" & CStr(Date) & "'"
'str = str & "WHERE dbo.tblshipping_sched.cust_ord_due = 3/28/05"

this is one works
str = str & "WHERE dbo.tblshipping_sched.cust_ord_due = '3/28/2005'"

doesn't work
str = str & "WHERE dbo.tblshipping_sched.cust_ord_due = 'cstr(date)'"
Syntax error converting character string to smalldatetime data type.

Your help is .appreciated

 

[RV]

Check out the SQL Server Date functions.

RV