DLookup In Query Doesn't Like Certain Dates

[seantnash]

I'm really confused by a query I'm building. It seems to work for most of my data, except for certain dates for some reason.

Here's a simplified verion of my table (StepCount):

StepsID .. MemNo ..... StepsDate ... DaySteps

1 ................... 1 .......... 20/08/2016 ..... 3434
2 ................... 2 .......... 21/08/2016 ..... 4434
3 ................... 1 .......... 15/08/2016 ..... 4434
4 ................... 1 .......... 21/08/2016 ..... 3454
5 ................... 1 .......... 10/08/2016 ..... 3234
6 ................... 1 .......... 19/08/2016 ..... 3474

Here's the relevant part of the query I am using:

DLOOKUP("DaySteps", "StepCount", "StepsDate=#" & STDA & "# AND MemNo=" & MEM)

What I want it to do is look up the StepsDate in the row and return the relevant DaySteps (I am using this version of the expression as a bit of a debug before going on to my next step in a calculation - rather than just simply needing it to return that column in the standard way). My query has columns for StepsDate (STDA) and MemNo (MEM)

It returns the correct value for most of the records, but some dates just result in null being returned (i.e. a blank value).

So, there's obviously something wrong with my expression but I'm really struggling to work out what as it works some of the time but not others.

Hopefully somebody will be able to help!

 

[arnelgp]

try this:

DLOOKUP("DaySteps", "StepCount", "StepsDate=#" & Format([STDA],"mm/dd/yyyy") & "# AND MemNo=" & [MEM])

 

[seantnash]

WOW! You're an absolute genius - it now works perfectly. Thank you soooo much!

Could you explain why adding the format bit works so that I know for future reference? I'm guessing it's something to do with American Vs. UK date formatting?

 

[arnelgp]

you area right there. you have to format it to american date first. this is true in any microsoft products.

 

[RuralGuy]

Here's Allen Browne's explanation about international dates: http://allenbrowne.com/ser-36.html