# 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 to `Null` evaluates to `Null`.

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:

All original code samples by Mike Wolfe are licensed under CC BY 4.0