DLookup - with Date

[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.

 

[Alex Dybenko]

try to format date as mm/dd/yyyy:

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

 

[Van T. Dinh]

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).