Date formats in DLOOKUP

[Rose B]

Hi,

I am trying to look up some values in a table based upon values in a form. I
know what is happening, and to some extent why.......but cannot work out how
to get over it! The lookup involves taking a date field, which being in the
UK is dd/mm/yyyy, and then using it to pick up values by comparing to a date
range in another table. The DLOOKUP works......excpet that it is using
mm/dd/yyyy. I have tried all kinds of ways of trying to format correctly but
have not hit the jackpot! Can anyone help? My code currently looks like...

varMinCharge = DLookup("MinClientCharge", "MileageRate", "DateFrom <= #" &
Me.ActivityDate & "# AND DateTo >= #" & Me.ActivityDate & "# AND VolService =
""" & Me.Activity & """")

Thanks,

 

[Douglas J. Steele]

varMinCharge = DLookup("MinClientCharge", "MileageRate", "DateFrom <= " &
Format(Me.ActivityDate, "\#yyyy\-mm\-dd\#") & " AND DateTo >= " &
Format(Me.ActivityDate, "\#yyyy\-mm\-dd\#") & " AND VolService = """ &
Me.Activity & """")

For more on this topic, see what Allen Browne has at
http://www.allenbrowne.com/ser-36.html or what I had in my September, 2003
"Access Answers" column in Pinnacle Publication's "Smart Access". You can
download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Rose B]

Many thanks for the fast response (you must deal with these questions all the
time)! I have ended up using a combination of your response plus others on
the forum that I was 'experimenting' with and it now seems to work. I have
now got....

varMinCharge = DLookup("MinClientCharge", "MileageRate", "DateFrom <= " &
Format(CDate(Me.ActivityDate), "\#mm\/dd\/yyyy\#") & " AND DateTo >= " &
Format(CDate(Me.ActivityDate), "\#mm\/dd\/yyyy\#") & " AND VolService = """ &
Me.Activity & """")

This is probably belt and braces, but hopefully OK. Thanks again,