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
If the values being compared differ, the condition evaluates to
This is pretty basic stuff.
Comparing Null and Non-Null Values
What happens if we compare a non-null value with a null value?
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 ...
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
Comparing Two Null Values
Something weird is going on with these
OK, let's get back to basics. If we use the equal sign to compare two identical values, that has to be
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 to
This behavior is documented in section 188.8.131.52 (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 184.108.40.206 (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: