0

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?

adrian
  • 97
  • 2
  • 12
  • Does it work if you use `AND [sample_date] = sampleDate`? - i.e. do not try to convert the date to a string. – Andrew Morton Sep 11 '20 at 09:01
  • No, the DLookUp thinks that 07/08/2020 is eight of july when it's really seven of august, if I change the date of the entry to 08/07/2020 the dlookup works anyways. – adrian Sep 11 '20 at 09:07
  • 1) Is `sampleDate` declared as `Date` type? It should be 2) If that doesn't work, you could try `Format(sampleDate, "yyyy-mm-dd")`. – Andrew Morton Sep 11 '20 at 09:10
  • 4
    Access expects `mm/dd/yyyy` (US format), not `dd/mm/yyyy`. `yyyy-mm-dd` works too. – Andre Sep 11 '20 at 09:15
  • Yes it is declared as Date type and it doesn't work with the other format, it doesn't work changing the format order. – adrian Sep 11 '20 at 09:19
  • So it's not possible for me to perform a dlookup with a date in dd/mm/yyyy? – adrian Sep 11 '20 at 09:25
  • @adrian What is the data type of the column `[sample_date]`? – Andrew Morton Sep 11 '20 at 09:58
  • `dd/mm/yyyy` in a vba dlookup is impossible as that makes access mix-up months and days. Best to use ISO `yyyy-mm-dd` (e.g. that string can be sorted). Date(Times) are stored as a number in db and only displayed in your region format. Try`Debug.Print Format(0, "yyyy-mm-dd")` – ComputerVersteher Sep 11 '20 at 11:02
  • 2
    _So it's not possible for me to perform a dlookup with a date in dd/mm/yyyy?_ You are mixing up things. A date value carries no format, though a format is applied for display. However, a _string expression_ for a date value must have a format that can't be misinterpreted. The only format that works for both DAO and ADO is the ISO sequence: yyyy-mm-dd. So, make it a habit to use that. – Gustav Sep 11 '20 at 12:12

1 Answers1

2

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.

Thomas G
  • 9,886
  • 7
  • 28
  • 41