Access date bug- dd/mm automatically got changed to mm/dd

Question

I coded something using Date statement in ms-access vba. It was working fine till the start of this month. But now I am seeing a very peculiar bug in access vba that the Date has automatically changed the format from dd/mm/yyyy to mm/dd/yyyy. Has anyone else encountered the same problem

Answer 1

The default Access SQL date format, regardless of locale, is mm/dd/yyyy. If you use an invalid date format, it will 'helpfully' try to convert that to a valid date for you.

So, if you use '30/09/2008', it will recognize you're using dd/mm/yyyy, and convert it appropriately. However, a value like '10/01/2008' is a valid mm/dd/yyyy value to begin with, so it will not be converted, and stored incorrectly in case you actually meant dd/mm/yyyy....

The solution is to always convert your date values to a mm/dd/yyyy string prior to using them in Access SQL statements. You have to be a bit careful here, as using VBA date format masks may not work entirely as you'd expect on non-US locales (e.g. 'helpfully' interpreting "mm/dd/yyyy" as "the localized short date format"), so please test carefully using your particular Access/VBA version.

Answer 2

Access requires a date to be unambiguous. It is generally recommended that you use yyyy/mm/dd, regardless of locale. For example:

strSQL="SELECT SomeDate FROM tblT WHERE SomeDate=#" & Format(DateVar, "yyyy/mm/dd") & "#"

Answer 3

I have been very successful using the datevalue() function. When getting dates from unbound controls it seems to be clever enough to interpret the format "dd/mm/yyyy" correctly. Thus, a Jet SQL query like

"Select * from DateTable where StartDate = datevalue(" & me!TxtStartDate & ");"

seems to work every time.