DLookup function is swaping dates

[jamaarneen]

Hi
I have a simple DLookup function to retrieve data from one field, based on a 'date' in another field.
it's almost working fine, but there is a problem: the function is swapping two dates: for the date 9/5/2009 (which means 9 May), he returns the value for 5/9/2009 (= 5/Sep).

In the table's design view, I have set the field to type 'date', format 19/6/2001 (Short Date).

there got to be some code to prevent VBA from being confused. I just don't know how.

this is my criteria part in the function:

"datparDate = #" & dDate & "#"

when I tried this:

"datparDate = #" & Format(dDate, "d mm yyy" & "#")

the function returned nothing (null)

(In addition, there are some records that he don't returns anything (=null). I hope to figure that one out later)

Thanks in advance
Ja - using MS 2003

 

[dhookom]

Dates always will use the m/d/y format in this type of expression. "yyy" is not the 4 digit year. Consider reading up on this at International Dates in Access.

I think this should work.

"datparDate = #" & Format(dDate, "mm\/dd\/yyyy")  & "#"

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

"datparDate=#" & Format(dDate, "yyyy-mm-dd") & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jamaarneen]

Thanks a lot Duane and PHV

both suggestions are working perfectly (and all blank records - disappear too!)

While I read in the link Duane mentioned that Access expects the "mm/dd/yyyy" format, I'm wondering Why PHV's suggestion works too?

And one more: Allen Browne is recommending always to use (for calculated fields) the CVDate function. What would you advise - is it worth? and should it be CDate or CVDate?

Thanks again
ja

 

[PHV]

Why PHV's suggestion works too?
Because it's unambiguous.

 

[RoyVidar]

PHV's suggestion is an ISO standard - i e, an international standard covering the exchange of date and time-related data.

It's quite safe to follow Mr. Browne's advice, he is a very competent Access developer, and as he points out, CDate() wil fail on Null, while CVDate()

Roy-Vidar

 

[jamaarneen]

Thanks - both - for the explanation.