Dlookup problems with yes/no field

  • Thread starter Thread starter Laurel
  • Start date Start date
L

Laurel

The "Bad Address" column in the tblRLC table is a Yes/No datatype. When I
look at the contents of the table, some have this column checked and some
don't. But when I execute the code below, lv_variant is ALWAYS 0. At first
I tried testing to see if it were True instead of testing for 0, but that
was never true. With the current code, no matter what the value in [Bad
Address], the function returns "BAD ADDRESS". If I test for True instead
of 0, fncBadAddress is always = to "". What am I doing wrong?


Dim lv_variant As Variant
fncBadAddress = ""
lv_variant = DLookup("[Bad Address]", "tblRLC", "Directory_HHD_Key =
True")
If DLookup("[Bad Address]", "tblRLC", "Directory_HHD_Key = True") = 0
Then
fncBadAddress = "BAD ADDRESS"
End If
 
How many records meet the criteria of Directory_HHD_Key = True? Dlookup will
only return the value of BadAddress from the first instance it encounters
and you *cannot* assume that records in a table have any particular order
(if you were searching a sorted query rather than a table that would be
different).

It may simply be that the first instance of Directory_HHD_Key = True has
BadAddress unchecked, but you'd need to know more about the specific record
its looking at to know one way or the other.


Dim lv_variant As Variant
lv_variant = nz(DLookup("[Bad Address]", "tblRLC", "Directory_HHD_Key =
True"),"")
If lv_Variant = 0 Then
fncBadAddress = "BAD ADDRESS"
Else
fncBadAddress = ""
End If
 
Back
Top