Serious problem with dates in the WHERE clause

[Guest]

Hello,

This is the report of a serious problem I have solved in an undocumented
way, as far as I know. However, the source of the problem continues unknown
to me. Sometimes I ask myself if this will be solved in Access 2007?

I have an application (mde) linked via ODBC to an SQL Server. In the WHERE
clause of SQL expressions, if I use a pair of delimiters # to enclose a date
inside, Access crashes immediately when reaches the query. I solve this
problem replacing # 20060506 # with CDate( “20060506†) in every query.

I ‘m convicted that this problem is related with the fact that I’m using
Windows XP Portuguese version and some type of erroneous conversion is done
with date. Portuguese date format is ddmmyyyy. However in vba code I’m always
using yyyymmdd format.

Thank you,
José António Silva

 

[Douglas J. Steele]

I'm unaware of Access accepting dates without the inclusion of a date
separator. In other words, you should have #2006-05-06# (or even
#2006/05/06#), not #20060506#. (I must confess I'm surprised that
CDate("20060506") works for you: I get a "Run Time Error Type 13: Type
Mismatch" when I try it)

Yes, there's an issue with Access and dd/mm/yyyy format. Unless the day is
13 or greater, Access will always treat the date as being in mm/dd/yyyy
format in SQL statements, regardless of what the Short Date format has been
set to in Regional Settings.

You might want to read Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html
or what I had in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Ken Snell \(MVP\)]

When you use the # delimiter for a date, ACCESS and Jet expect the date to
be in the mm/dd/yyyy format. So just cast the date value into that format
and then you should be able to use the # delimiter.

 

[Guest]

Thank you all,

I will test the #dd/mm/yyyy# format. I never have done this, because I use
from the begining the #yyyy-mm-dd# format.

Sorry, I wrote in my post #yyyymmdd" when I should have wrote #yyyy-mm-dd#

José António Silva

 

[Douglas J. Steele]

Reread what Ken & I wrote: you cannot use #dd/mm/yyyy# format, regardless of
what your regional settings might be.

Check the references I gave you in my first post.

 

[Guest]

Sorry again,

Maybe this is to much work. What I really want to test is the American
Format #mm-dd-yyyy#.

I have already read your referenced Allen Browne page and it was very
helpful to me.

José António Silva