The Strange Behavior of Null
When are two identical values not equal? When they're both Null! If you are a Microsoft Access developer, you need to understand how (and why) this works.
What does "Null" mean? I don't know.
Let me rephrase that. A "Null" value literally means, "I don't know." It could be anything. Or nothing. Or the third baseman. Or the answer to life, the universe, and everything. Think of "Null" as a placeholder for an as-yet-unknown value. It's the to-be-determined of the database world. Bear this provenance in mind, and the rest of this article might just make some sense.
Because it's about to get weird.
Comparing Non-Null Values
The equals sign is pretty straightforward.
If the values being compared are the same, the condition evaluates to True.
If the values being compared differ, the condition evaluates to False.
This is pretty basic stuff.
Comparing Null and Non-Null Values
What happens if we compare a non-null value with a null value?
The condition 1 = Null evaluates to False. That makes sense.
Negation of Null
Surely, the opposite of that must be True. Let's wrap the 1 = Null in a Not() and see what happens:
Egads! The opposite of False is ... False? Inconceivable!
Not Equals With Null
Instead of using the Not keyword, what if we use the "Not Equals" operator (<>)? The number one (1) does not equal Null, so surely this will evaluate to True:
Crikey! Inconceivable!
Comparing Two Null Values
Something weird is going on with these Null values.
OK, let's get back to basics. If we use the equal sign to compare two identical values, that has to be True. Right?!?!
False. Again?! Inconceivable!
The Counterintuitive Behavior of Null
There are two different behaviors of Null that lead to the examples above.
1. Null Compared to Anything is Null
Any data type compared toNullevaluates toNull.
This behavior is documented in section 5.6.9.5 (Relational Operators) of the VBA Language specification.
The effective value type is determined as follows, based on the value types of the operands:
What the above table is showing is that for relational operators (=, <>, <, <=, >, >=), comparing a Null to "Any numeric type, String, Date, Empty, or Null" will result in an "Effective Value Type" of Null.
2. Null Coerced to a Boolean Expression is False
Section 5.6.16.3 (Boolean Expressions) of the VBA Language Specification deals with "Let-coercion" of generic expressions to boolean expressions:
boolean-expression = expression
[I]f <expression> has the data value Null, the value of <expression> is False.
In plain English, this is basically saying that when push comes to shove Null is equivalent to False.
One Final Oddity...
You Can't CBool() a Null
Despite the above behavior, if you try to pass a Null value to the CBool() function, you get an "Invalid use of Null" error. I have to admit, I find this a bit strange and inconsistent:
