All of a sudden (but I can't think of what changed on my system to cause this), Date$() returns 07-05-2005 (mm-dd-yyyy) and Date() returns 05-07-2005 (dd-mm-yyyy). On my system, it should be (dd-mm-yyyy). The two functions are supposed to be equivalent except that the Date$() function returns a string. I've tried formatting the string different ways and it seems that Date$() actually is reading the real date wrong. For example (just to prove my theory):
Date$() returns "07-05-2005" but should return "05-07-2005"
Format$(Date$(), "dd/mm/yyyy") returns "07/05/2005" but should return "05/07/2005"
Format$(Date$(), "Short Date") returns "7/05/2005" but should return "5/07/2005"
Format$(Date$(), "Long Date") returns "Saturday, 7 May 2005" but should return "Tuesday, 5 July 2005"
Format$(Date$(), "yyyymmdd") returns "20050507" but should return "20050705"
Can anyone please tell me why this is and how do I fix it?
HOW I INVESTIGATED THIS:
I noticed that an Access VBA form was displaying its default date value as mm/dd/yyyy but it is supposed to display as dd/mm/yyyy (using functions Format$(Date$(), "dd/mm/yyyy").
I checked the underlying table (which has the same default value) and noticed that pre-existing data was in the correct format but the "new record" line had the default value in the wrong format.
I checked my regional settings and they looked fine. I tried resetting them and restarting my machine but that didn't make any difference. My current settings are:
Standards and formats = "Australia"
Short date = 5/07/2005
Long date = Tuesday, 5 July 2005
On the Advanced tab, I tried changing Language for non-Unicode programs from "English (United States)" to "English (Australia)" and checking the "Apply all settings to the current user account and to the default user profile". This caused me to restart the machine after which I checked my Access DB and saw the odd behaviour remains.
I decided to check both VB6.0 and C#. VB6.0 obviously uses the same function as VBA so the errant behaviour was observed there. C# seems to work correctly.