“=†Versus “<>†when working with Nulls in VBA Code

[Brad]

If I use “=†when comparing two fields and one field contains nulls, I obtain
the results that I would expect.

However, if I use “<>†when comparing the same two fields I see a result
that I do not expect.

I have trimmed down the code to the following pseudo-code examples to
simplify things.

~ ~ ~
Field1 is null
Field2 is = “Aâ€


~ ~ ~ TEST1
If Field1 = Field2 Then
Msgbox “Equalâ€
Else
Msgbox “NOT Equalâ€
End if


~ ~ ~ TEST2
If Field1 <> Field2 Then
Msgbox “NOT Equalâ€
Else
Msgbox “Equalâ€
End if


~ ~ ~

TEST1 works as I would expect, and returns a message of “NOT Equalâ€
(This is because “A†is not equal to Nulls)

TEST2 returns a message of “Equalâ€
HUH????? Why does it indicate that “A†is equal to Nulls)

~ ~ ~

Can someone explain this to me?

Please type real slow, as I am having a difficult time surrounding this

I realize that I can simply avoid using “<>†in future VBA code, but I would
like to understand the rest of the story.

Thanks,

Brad

 

[Banana]

Field1 is null
Field2 is = “Aâ€


~ ~ ~ TEST1
If Field1 = Field2 Then
Msgbox “Equalâ€
Else
Msgbox “NOT Equalâ€
End if


~ ~ ~ TEST2
If Field1 <> Field2 Then
Msgbox “NOT Equalâ€
Else
Msgbox “Equalâ€
End if


~ ~ ~

TEST1 works as I would expect, and returns a message of “NOT Equalâ€
(This is because “A†is not equal to Nulls)

TEST2 returns a message of “Equalâ€
HUH????? Why does it indicate that “A†is equal to Nulls)

Actually, no. What Test2 is really saying that "It is false that A does
not equal nulls". Before you think that it's semantics, consider this:

?Null = Null
Null

In other words, we don't know whether null would equal to itself. If you
substitute the word 'null' with 'unknown', it may become apparent that
we have no idea of how to determine whether it is true or false that any
value could equal to an unknown value and thus works both ways whether
it's a = test or <> test. Did that help?

 

[Dorian]

My guess is that '<>' means 'less than or greater than' and you cannot do
those comparisons with Null since Null means 'unknown'.
Did you try 'NOT =' instead of '<>', that should work.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[vanderghast]

As already explained, a comparison with database-null always return null,
even database-null = database-null returns null. Is my car color matches my
hair color? my car color is unknown to you, so its value is null, same for
my hair color, so is null equals null? Maybe the two colors match, maybe
not, in fact you don't know and that means it is unknown, null... and you
have your answer to null = null ? The answer is null.


You can use IsNull( arg ) in VBA, or expression IS NULL in SQL to
test for a null value.


About if-then-else statement in VBA, the if-then part is applicable for
what is TRUE, the ELSE part is applicable to ANYTHING ELSE, which stands
for FALSE as well as for NULL (unknown).

If IsNull( arg ) then
...
Else
...
End if


would effectively handle the then part if the argument value is null, and
the else part, otherwise.


( In C, C++ and C#, a null has another meaning, which is like the value
Nothing in VB/VBA, but that null has nothing to do with the database-null
value. )


Vanderghast, Access MVP

 

[Brad]

Thanks for the help. I appreciate it.
--
Brad

Actually, no. What Test2 is really saying that "It is false that A does
not equal nulls". Before you think that it's semantics, consider this:

?Null = Null
Null

In other words, we don't know whether null would equal to itself. If you
substitute the word 'null' with 'unknown', it may become apparent that
we have no idea of how to determine whether it is true or false that any
value could equal to an unknown value and thus works both ways whether
it's a = test or <> test. Did that help?
.

 

[Brad]

Thanks for your insights. This now seems to make sense.

I was really puzzled when I first ran into this.

 

[Roger Carlson]

See: What does NULL mean? How is it different than the Empty String?
http://rogersaccessblog.blogspot.com/2008/10/what-does-null-mean-how-is-it-different.html


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Brad]

Roger,

Thanks for the link, that is good info.

I appreciate your assistance.

Brad