The Dreaded Access Date Bug

I use Microsoft Access 97 a lot. It runs well on Windows XP and all previous versions of Windows since it was released. The only problem is that its date system is very, very broken in one respect: it only understands the mm/dd/yyyy format. Anytime you use dd/mm/yyyy you are asking for trouble.
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 String
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")

The displayed values are:

08/01/2007    08/01/2007
08-Jan-2007   08-Jan-07

So 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/2008
07-Jan-2008   07-Jan-08

Confused? 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.