Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DLookup function is swaping dates

Status
Not open for further replies.

jamaarneen

Programmer
Dec 27, 2007
212
BE
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:
Code:
"datparDate = #" & dDate & "#"

when I tried this:
Code:
"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

 
What about this ?
Code:
"datparDate=#" & Format(dDate, "yyyy-mm-dd") & "#"

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

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
 
Why PHV's suggestion works too?
Because it's unambiguous.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top