Can't test for <> null

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

Laurel

The 2nd line of this code is never executed, even when
lrst_NamesAndAddress!Spouse_First_Name shows a value when I hover over it in
the debugger. If there is not a name in the column then when I hover over
that variable, the value shows as Null. I tried "is not null" instead of
<> Null, but that always returns an error. What am I not seeing here?

If lrst_NamesAndAddress!Spouse_First_Name <> Null Then
ls_FirstLine = ls_FirstLine & " & " &
lrst_NamesAndAddress!Spouse_First_Name
End If

TIA
LAS
 
Try this:

If Not Nz(lrst_NamesAndAddress!Spouse_First_Name, "") = "" Then
ls_FirstLine = ls_FirstLine & " " & lrst_NamesAndAddress!Spouse_First_Name
End If

Steve
 
"Laurel" wrote
2nd line of code never executed, even when
lrst_NamesAndAddress!Spouse_First_Name
shows a value when I hover over it in the
debugger. If there is not a name in the column
then when I hover over that variable, the value
shows as Null. I tried "is not null" instead of
<> Null, but that always returns an error.
What am I not seeing here?
Click to expand...

Null, by definition, is not a value but the absence of a value, so it is
neither equal nor not equal to anything, even another Null. You test for it
using the IsNull built-in function. So instead of
If lrst_NamesAndAddress!Spouse_First_Name <> Null Then
Click to expand...

try using

If Not IsNull(lrst_NamesAndAddresses!Spouse_First_Name) Then

and see if that doesn't work better. You use "Is Null" or "Is Not Null" in
the criteria of Queries, but use the IsNull function in VBA code or Access
expressions.

Larry Linson
Microsoft Access MVP
 
I should point out:
Using the Nz() function the way I showed you tests for both Null and a zero
length string "". If you do not want to test for a zero length string aswell,
use the IsNull() function.

Steve
 
Actually, while it may seem counterintuitive, the following is better:

If Len(lrst_NamesAndAddress!Spouse_First_Name & vbNullString) > 0 Then
ls_FirstLine = ls_FirstLine & " " & lrst_NamesAndAddress!Spouse_First_Name
End If
 
Back
Top