vba access Dlookup with dates in dd/mm/yyyy forma

Question

I'm trying to get a value using a DlookUp, the problem is access formats my dd/mm/yyyy into mm/dd/yyyy despite using the Format function.

muestraAguasDatos = Nz(DLookup("[name]", "samples", "[location] = '" & location & "' AND ([name] LIKE 
'*ACRT*' OR [nombre] LIKE '*CAWQ*') AND [sample_date] = #" & Format(sampleDate, "dd/mm/yyyy") & "#"))

This DLookup works when day value are > 12 but when it's lower and despite having the format it still format it to mm/dd/yyyy

Can you help me solving this issue please?

Answer 1

There are so many misunderstanding with MS Access date fields for non-US residents.

The basic rule is :

Whenever you specify a hardcoded date literal using #the date# notation, in either :

• an SQL query
• a query filter criteria
• in VBA
• in a Dlookup() like you do

You should ALWAYS use the US date format : MM/DD/YYYY, or the ISO format YYYY/MM/DD

The confusion among Access beginners, comes from several things :

1. In the interfaces, by default, MS Access does an implicit conversion of the dates in the format that is defined on Windows Regional and Language Options setting of the computer. So non-US residents might have the impression that dates are always stored by default in the DD/MM/YYYY format, but that cake is a lie. Dates are stored as numbers, and it is just the display format that changes and is adapted following the computer settings.

2. In some cases, when you code date literals with #the date# in VBA or a Query, using DD/MM/YYYY format, it just works fine. The reason is date there's a check date algorithm in MS Office that validates a date and modify it to the right format in certain circumstances:

• When your date begins by the year, MS Access is smart enough to detect it and it will then consider that your date is enterred in YYYY-MM-DD and do an implicit convertion to MM/DD/YYYY.
• If the month part is higher than 12 and lower then 31, then MS Access will understand that this is in fact a DAY, and that you hardcoded the month at the other place. So for instance if you introduce 15th of September like this : #15/09/2019# it will implicitly be transformed in #09/15/2019#. However if you enters the 11th September like this #11/09/2019#, then Access will process it as 09th November !!!

Personal opinion, I have always found this last behavior plain stupid, because it may introduces a lot of troubles on applications of people not acquainted by that mechanism, and that tracking where the problems comes can be very tedious. It's sneaky, it should not be there. Much better to raise an error if the format is wrong.