Return DLookup result as date?

[kman42]

Does DLookup return its result as a string?



I have a query that uses an DLookup expression to return a date from another table, but when I try to sort on the field, it appears to be sorting it as a string rather than as a date. Is there a way to format the field as a date so it sorts properly?

Here's my expression:
DLookup("DateOfEvent","tbl_EventLog","fk_EventID=" & EventID)

This returns the dates okay, but when I sort them in ascending order I get something like this:

4/14/2011
4/20/2011
4/8/2011

I can sort them in descending order and they switch, but they obviously aren't ordering by the date which is what made me think the DLookup was returning the result as a string instead of as a date.

All my date fields are formatted as mm/dd/yyyy as time is not important.

thanks!

[gnoakes]

Hi,

I would put a format function around your expression such as...

FormatDateTime(Dlookup("DateofEvent","tbl-EventLog","fk_EventID="&EventID),2)

The "2" denotes for a "short date" format.

Hope that helps,

George

[kman42]

It's still not sorting correctly.

[RuralGuy]

Have a look at the CDate() function.

[kman42]

I just happened upon that and have changed to this code, but still no luck:

DateOfP: IIf(DLookUp("DateOfEvent","tbl_EventLog","fk_Event ID=1)<>"",CDate(DLookUp("DateOfEvent","tbl_EventLo g","fk_EventID=1)),"")

Some of the dates are empty because that particular type of event hasn't occurred yet so I have to allow for empty returns as well.

It's still not working with this setup though.

[kman42]

I did this:
DateOfP: CDate(Nz(DLookUp("DateOfEvent","tbl_EventLog","fk_ EventID=1),"0"))

This returns "12:00:00 AM" for the empty fields and since all the others are strictly dates, I was able to use a WHERE clause to get rid of them. This seems like the wrong way to do things, but I at least managed to obtain the proper set of records in the right order.

[RuralGuy]

Have you single stepped the code to see what is being returned?

[RuralGuy]

Just so you understand that *all* DateTime fields have a TIME portion. http://support.microsoft.com/kb/q130514/en-us

[kman42]

Sort of. Once I had it working with a bare minimum of functions (just using cDate), I started adding other functions to try to get rid of the empties. When I added Iif, it stopped working, so I guess it returns strings as well even if the return statement is cDate. Strange.