Date issue in Access 2003 query

[ShadrakUK]

I have a very simple query looking for a date in a file, I ran it and it
worked, I then cut and pasted the SQL into my VBA code (turning it into a
string etc.) and it didn't work. I then noticed the date in the SQL was
wrong, so I went back to the original query and I noticed something odd.

In design view the Criteria testing for the date shows as #01/03/2008# but
when I switch to SQL view it shows as #3/1/2008# (day and month swaped). I
checked my PC setup and it's all set to English UK I also tried the same
query on my work PC and it did the same.

Anyone have any idea why this is the case?

 

[John W. Vinson]

I have a very simple query looking for a date in a file, I ran it and it
worked, I then cut and pasted the SQL into my VBA code (turning it into a
string etc.) and it didn't work. I then noticed the date in the SQL was
wrong, so I went back to the original query and I noticed something odd.

In design view the Criteria testing for the date shows as #01/03/2008# but
when I switch to SQL view it shows as #3/1/2008# (day and month swaped). I
checked my PC setup and it's all set to English UK I also tried the same
query on my work PC and it did the same.

Anyone have any idea why this is the case?

Because the people who wrote the code for Access were based in the US and made
an arbitrary decision that all literal dates in SQL queries would be
interpreted using the US month-day-year format. #3/1/2008# will be read as
March 1; regional settings are IGNORED.

You can either coerce the date into the desired format by using the Format()
function - which *does* honor regional settings:

Format([yourdate], "mm/dd/yyyy")

as a criterion; or using an unambiguous international date format such as the
ISO standard yyyy-mm-dd.