Date comparison in Access SQL

R

Roy Ristie

Hi, does anyone know where i can find a good explanation of the date
formats, the date functions and their uses in access sql.

I have a query which has this sub-clause in it's where-clause:
" AND DeliveryDate>=#" & Me!txtDate & "#.......etc, etc.

Until today (Feb 14) I thought that everything worked ok, but when the sql
was executed no rows were returned, where 2 rows should have been.
I removed the '#'-signs and executed the sql again and this time the
expected rows were returned.

I am due to handover the db to the client and as you can understand i'm
worried now about the whole date issue.
btw. Me!txtDate contained 07-02-05 (last monday) in both cases above.

What may also be relevant is that the date is in international format eg,
dd-mm-yy.

thanks in advance for any help.

Roy
 
R

Rick Brandt

Roy Ristie said:
Hi, does anyone know where i can find a good explanation of the date formats,
the date functions and their uses in access sql.

I have a query which has this sub-clause in it's where-clause:
" AND DeliveryDate>=#" & Me!txtDate & "#.......etc, etc.

Until today (Feb 14) I thought that everything worked ok, but when the sql was
executed no rows were returned, where 2 rows should have been.
I removed the '#'-signs and executed the sql again and this time the expected
rows were returned.

I am due to handover the db to the client and as you can understand i'm
worried now about the whole date issue.
btw. Me!txtDate contained 07-02-05 (last monday) in both cases above.

What may also be relevant is that the date is in international format eg,
dd-mm-yy.
Click to expand...

Numeric Date literals in a query are always treated as US date format
(mm/dd/yy) so your query was actually testing for July 2, 2005.

You can use ISO format (yyyy-mm-dd) or a format with month alpha characters
(07-Feb-2005) and they will work consistently, but if you use a two digit month
Access will interpret it in US format except in cases where doing so results in
a month value greater than 12.
 
Top