every record since #date#

[ChironeX]

i have this weird thing, my hair is getting grey...
when i run the query
SELECT *
FROM table1
WHERE (((table1.fielddate)<#7/6/2006#));

it returns every record that matches

table1.fielddate<#6/7/2006#

in design mode you can see 6/7/2006 at the criteria
it puts the month in stead of the day and vice versa...

 

[Studentos]

...WHERE your_date < CDate("2006-01-31")

 

[neileg]

SQL only works with dates in US format. Access tries to accomodate UK format dates but often fails where you use dd-mm-yyyy. I usually use a medium date format like 07-Jun-2006 which seems to avoid the problem.

 

[ChironeX]

SQL only works with dates in US format. Access tries to accomodate UK format dates but often fails where you use dd-mm-yyyy. I usually use a medium date format like 07-Jun-2006 which seems to avoid the problem.

indeed, when i use the function format(date,"mm-dd-yyyy") to convert my date en then run the query, the problem is solved.
thanks!