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

B

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
 
B

Banana

Brad said:
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?
 
D

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".
 
V

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
 
B

Brad

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


Banana said:
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?
.
 
B

Brad

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

I was really puzzled when I first ran into this.
 
J

JimBurke via AccessMonster.com

In VBA '<>' is 'not equal to'. 'NOT =' may work also, I've never tried it,
but using '<>' is correct.
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".
If I use “=†when comparing two fields and one field contains nulls, I obtain
the results that I would expect.
[quoted text clipped - 44 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top