Problem with date formats

[Guest]

Hi there,

I have a bug that I cannot trace. I use a short date format of d/m/yyyy.
This is used throughout my database. However when I use dlookup through VBA,
the query matched a date format of m/d/yyy.

For example when I lookup a date of 12/09/1989 (12 Sept), the query matches
it as a date of 09/12/1989( 09 Dec).

I have gotten around the problem by chopping up the date and reconstructing
it using some of the date funtions, but it seems to be a complicated way to
solve what must be a simple bug.

Any ideas.

Thanks
Kenny

 

[Rick Brandt]

Hi there,

I have a bug that I cannot trace. I use a short date format of d/m/yyyy.
This is used throughout my database. However when I use dlookup through VBA,
the query matched a date format of m/d/yyy.

For example when I lookup a date of 12/09/1989 (12 Sept), the query matches
it as a date of 09/12/1989( 09 Dec).

I have gotten around the problem by chopping up the date and reconstructing
it using some of the date funtions, but it seems to be a complicated way to
solve what must be a simple bug.

In Access code and queries you have to use mm/dd/yyyy format or a format
that uses alpha characters for the month. Just the way it is.

 

[Douglas J. Steele]

In Access code and queries you have to use mm/dd/yyyy format or a format
that uses alpha characters for the month. Just the way it is.

Sorry to argue, Rick, but that's not 100% correct. You can use yyyy-mm-dd
safely, since it's not ambiguous. (In fact, 25/12/2004 will be treated
correctly as well, as it's not ambiguous)

Kenny: You might find it informative to read Allen Browne's "International
Dates in Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html or
what I have at http://members.rogers.com/douglas.j.steele/SmartAccess.html