I coded something using Date statement in Access VBA. It was working fine until the start of this month, but now I am seeing that the Date has automatically changed the format from dd/mm/yyyy to mm/dd/yyyy. Has anyone else encountered the same problem?
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.