DLookup - with Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone figure out why this DLookup expression is returning Null to BH :-

Dim FDate As Date
Dim BH As Variant

FDate = "29/08/04"

FDate = FDate + 1

BH = DLookup("[BHID]", "tblBH", "[BHDay] = #" & FDate & "#")

.... and yes, there is a table (tblBH) with fields (BHID and BHDay) and one
row has BHDay value of 30/08/04.

Thanks.
 
try to format date as mm/dd/yyyy:

BH = DLookup("[BHID]", "tblBH", "[BHDay] = #" & format(FDate,"mm\/dd\/yyyy")
& "#")
 
Date values enclosed by hashes (#) must be in USformat of mm/dd/yyyy
regardless of your regional setting. Try:

BH = DLookup("[BHID]", "tblBH", "[BHDay] = " &
Format(FDate, "\#mm/dd/yyyy\#")

(type as one line).
 
Back
Top