DD/MM/YYYY vs MM/DD/YYYY

[ryan.gillies]

Hi all - I have an extremely frustrating problem with date formats in Access/VBA and hope you can help!

We generate a large number of reports here, and often end users will be looking at dates in particular. I just about been able to ascertain that regardless of what date it is, Excel always uses the MM/DD/YYYY format.

Access and the associated VBA we generate for it is proving less reliable however. Towards the end of last month it was very happy accepting dates in DD/MM/YYYY format (presumably because you can't have the 11th of the 30th month in 2011). However now its switched around, and is treating dates as MM/DD/YYYY - so today's date (8 Dec 2011) is actually being treated as 12 Aug 2011.

I've tried using format to structure the date this way, but Access/VBA still sporadically treats in reverse anyway, and changes it back to the other format - there seems to be no consistency to it! I've even tried using DateSerial and it still gets it wrong.

I'm slowly going insane - is there a better way/different approach I can take to using dates? I know dates are stored as a number, can I use that format instead to make it more reliable?

 

[spikepl]

Dates in SQL are always mm/dd/yyyy http://allenbrowne.com/ser-36.html

 

[ryan.gillies]

Extremely helpful thank you, didn't realise it was the Windows regional settings impacting on the problem.

 

[Galaxiom]

It is unfortunate that Access will second guess an invalid date and reverse it. It just makes the problem worse. Much better that it would reject the invalid date.

Native JET/ACE SQL date format is mm/dd/yyyy but it will accept yyyy/mm/dd too.

However it is worth noting that even the forward slash is not what you think. It is not a literal slash but a placeholder for the regional date separator.

The ultimate way to format a date for Jet/ACE SQL from all regions is:

Format({expression}, "\#mm\/dd\/yyyy\#")

The backslash is the literal escape that ensures the separator is actually the backslash that JET/ACE will undertand regardless of the regional separator.

If you want to include Time:

Format({expression}, "\#mm\/dd\/yyyy hh\:nn\:ss\#")

 

[spikepl]

Access will "eat" unambiguous dates like yyyy/mm/dd or dd/mmm/yyyy.

Ad the issue is not the locale setting as such, because Access does the proper translation internally, when needed.

The issue bites you when you yourself go from date to string representation or vice versa, eg in a contructed SQL statement, where the string representation of the date needs to be formatted as Galaxiom outlined.