Today's date of 08-Jan-2007 will transpose into 01-Aug-2007 or 07-Jan-2008 in the blink of an eye, even if you are very careful. Try this
Dim dtmDate As Date, strDate As StringThe displayed values are:
Const MY_DATE As String = "08-Jan-2007"
dtmDate = DateValue(MY_DATE)
strDate = Nz(dtmDate, Date)
Debug.Print strDate, dtmDate
Debug.Print Format(dtmDate, "dd-Mmm-yyyy"), Format(dtmDate, "Medium Date")
08/01/2007 08/01/2007So far so good, you may say. Actually not. Every time a date is converted to a string, or a string to a date, unless you specifically state otherwise, the "mm/dd/yyyy" method is used.
Change the "08-Jan-2007" string to read "08-Jan-07" (i.e. what "Medium Date" is displaying, and the result changes to:
07/01/2008 07/01/2008Confused? I was. You can't set "Medium Date" in International settings either. My "Short Date" setting is always set to "dd/MM/yyyy". After all, I'm using the Metric units system, and the UK English dictionaries, and an A4 page size. So never use "Medium Date"; always specify it as "dd-Mmm-yyyy" instead.
What to make it worse? Try getting the value of a date control from a form, even if the format of the date control is "dd-Mmm-yyyy". If you try something like this:
dtmDate = nz(frm!MyDate)The answer will be wrong, because somewhere in this process the value of the control is onverted from a date to a string, and then back to a date. The only foolproof way of doing this is to change the code to read:
dtmDate = Format(frm!MyDate,frm!MyDate.Format)Then, provided you set the control format to "dd-Mmm-yyyy" it will work right every time.
Now for the really fun part: you can replicate these errors in Access 2002. I'm willing to bet that the same problem will be reproduced in Access 2003 and Access 2007.
Update: the bug can be repeated easily in Access 2007 SP1 (12.0.6211.1000). Just change your regional settings to "English (South Africa)" and your short date to "dd/MM/yyyy" in either WinXP or Vista.