DLookup Invalid Use of Null

[Guest]

I have the following statement:

txtCheckRecord = DLookup("[JLocation]", "JlHouse", "[JDate] = #" &
dtTestDate & "#")

When there is not a record in the JHouse table with a date that matches the
dtTestDate field, I receive the error "Invalid Use of Null". The DLookup
works fine if there is a matching date.

Any ideas?

Thanks

Randy Hartwick

 

[Dirk Goldgar]

I have the following statement:

txtCheckRecord = DLookup("[JLocation]", "JlHouse", "[JDate] = #" &
dtTestDate & "#")

When there is not a record in the JHouse table with a date that matches the
dtTestDate field, I receive the error "Invalid Use of Null". The DLookup
works fine if there is a matching date.

Any ideas?

If there's no record meeting the DLookup criteria, the function will
return Null. If txtCheckRecord is a text box, as its name implies, then
that shouldn't be a problem. But if it's a String variable, it can't
hold a Null value. What value do you want it to contain if there's no
matching record? You can use the Nz() function to set it to whatever
you want in that case. For example, this will set it to a zero-length
string:

txtCheckRecord = _
Nz(DLookup("[JLocation]", "JlHouse", _
"[JDate] = #" & dtTestDate & "#"), _
"")