help ado sql query syntax probleme with dates in cretiria 1

[angelpr23]

hello everybody,
i have a sql query sql = _
"select Orders.ReceiptDate, Orders.OrderID," & _
"OrderDetails.OrderID, OrderDetails.ProdSumPrice " & _
"where Orders.OrderID=OrderDetails.OrderID and " & _
"Orders.ReceiptDate between #1/1/2007# " & _
"and #31/12/2007#"

Set rs = New ADODB.Recordset
rs.Open sql, conn, adOpenStatic, adLockOptimistic

When i am trying to open the sql query with rs ado recordset i am getting runtime error -214721700(80040e14)
which says that operator is missing in the expression 'OrderDetails.ProdSumPrice where Orders.OrderID=OrderDetails.OrderID and Orders.ReceiptDate between #1/1/2007# and #31/12/2007#'

I tried this query in microsoft access and it works fine.
I believe that the problem is in between #1/1/2007# and #31/12/2007# but i don't know what exactly could be maybe the character # but i also tried without # and i am still getting the same error. Any help will be much appreciated.

Thank you
all in advanced.

 

[Swi]

http://www.varig.com/vftc/sql/betweenand.html

Swi

 

[angelpr23]

Thank you Swi i tried my query
sql = _
"select Orders.ReceiptDate, Orders.OrderID," & _
"OrderDetails.OrderID, OrderDetails.ProdSumPrice " & _
"where Orders.OrderID=OrderDetails.OrderID and " & _
"Orders.ReceiptDate between '" & 1/1/2007 & "' " & _
"and '" & 31/12/2007 & "'"

but i am still getting the same error

 

[Swi]

Try this:

sql = "SELECT Orders.ReceiptDate, Orders.OrderID," & _
    "OrderDetails.OrderID, OrderDetails.ProdSumPrice " & _
    "WHERE Orders.OrderID=OrderDetails.OrderID AND " & _
    "Orders.ReceiptDate BETWEEN '1/1/2007' AND '31/12/2007'"

Swi

 

[SBerthold]

Change
#31/12/2007#
to
#12/31/2007#

 

[gmmastros]

>> Orders.ReceiptDate between #1/1/2007# and #31/12/2007#

There is a subtle problem with this code. If the ReceiptDate field contains date AND time, you will miss all records on dec 31, 2007 except those that occur at exactly midnight. When you specify a date (and not a time) a midnight time is assumed. So, 31/12/2007 is actually 31/12/2007 0:00:00.000. So, a record that occurs at noon on that day will be, 31/12/2007 12:00:00.000. This is greater than the end date so it will not be returned. I syntax I suggest is...

[tt][blue]
Orders.ReceiptDate >= #1/1/2007#
And Orders.ReceiptDate < #1/1/2008#
[/blue][/tt]

Notice that the end date condition uses less than (not Less than or equal to). This is important so that you don't get any records from 1/1/2008.

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Star for George. While this may not solve the OP's problem as stated, it forestalls any very difficult to find data errors that can come up because of this. It's the sort of error that comes up in a 5 year old production application and gives programmers fits, unless someone has the experience to look for it specifically.

Bob