DLookup on Date/Time field returns text date

[Ambre]

Hi,

I live in the UK so my computer uses dd/mm/yyyy date format.

When I use DLookup in a query on a Table with a Date/Time field it returns a string version of the date. This means that a sort on this returned field is not chronological. All dates beginning 01 sort first regardless of month or year.

Why is this? Do I need to tell DLookup to return the stored format of the date in some way?

I would appreciate any suggestions to correct this.

Ambre

[Gicu]

Weird, but try to wrap the DLookup in a CDate (like in CDate(DLookup("YourField","tblTable")).
Cheers,

[Bob Fitz]

Hi,

I live in the UK so my computer uses dd/mm/yyyy date format.

When I use DLookup in a query on a Table with a Date/Time field it returns a string version of the date. This means that a sort on this returned field is not chronological. All dates beginning 01 sort first regardless of month or year.

Why is this? Do I need to tell DLookup to return the stored format of the date in some way?

I would appreciate any suggestions to correct this.

Ambre

I'm in the UK but can't replicate your issue.
Can you post an example db

[CJ_London]

What is the backend? Access or sql server? Something else?

[Gicu]

Are you using the Date\Time Extended data type?
https://www.access-programmers.co.uk...-query.327239/

Cheers,

[Ambre]

Hi,

Thanks for the quick response.

Wrapping the expression in CDate works, but... throws an error if the looked for date is missing!

Ambre

[Welshgasman]

Hi,

Thanks for the quick response.

Wrapping the expression in CDate works, but... throws an error if the looked for date is missing!

Ambre

So use the NZ() function.

[Micron]

I'd be leery of this approach. First, if you don't use criteria in the lookup (so far OP has not shown their expression being used) you get a random value returned. If its month value is 12 or less for European dates (dd/mm/yyyy) as in 03/11/2023 it should be interpreted as March 11, not November 03 as intended. If the date is 15/03/2023 Access should interpret that as a non-US formatted date and use the value of March 15 as intended. So 2 different interpretations of the same date format.

see http://allenbrowne.com/ser-36.html

[Welshgasman]

It does not produce a string but a date?

From the Immediate window

? vartype(dlookup("On_Date","Real_dates","ID=8"))
 7

7 is Date type?
https://learn.microsoft.com/en-us/of...rtype-function

[Micron]

Yes, the documentation states it returns the value found. If it returns a string, then the field must be text regardless if it looks like dates. I didn't mention that because I focused on the idea of coercing what I figured was a string into a date as suggested by using CDate because in the end, you'd have dates. I also figure that some of the date values would get screwed up during that conversion. So I would not wrap CDate in NZ either if the field is text. If the field is date data type, then perhaps a format has been applied, making the sort look weird for dates.

[Ambre]

Many thanks to everyone who responded to this post. Your comments have introduced me to aspects of Access that I was totally unaware of.

I still don't fully understand why access behaves as above, but I have found a work around that works for me. If I wrap the DLookup in NZ() and provide NZ with a date so far in the past eg. #01/01/1600# that it is obviously wrong, everything works fine.

Regards,
Ambre