Can't test for <> null

[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

 

[SteveM]

Try this:

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

Steve

 

[Larry Linson]

"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?

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

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

 

[Laurel]

That did it. Thanks.
Is there some rule for stuff like this????

 

[Allen Browne]

Laurel, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

The article explains why you can't compare anything to Null, and 5 other
mistakes people often make.

 

[Laurel]

Thanks for the explanation of why things work this way. This also works.

 

[Laurel]

Thanks, that's a GREAT little article!

Laurel, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

The article explains why you can't compare anything to Null, and 5 other
mistakes people often make.

 

[SteveM]

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

 

[Douglas J. Steele]

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