Flaws in Microsoft Access

Provided by allenbrowne.com, October 2004. Bug reported by Kai Langosch.

Comparison gives wrong result

The query engine in Access yields the wrong results if you use the inequality operator to compare anything to Null.

This flaw has is present in JET 4 (Access 2000 and later), JET 3 (Access 95 and 97), JET 2 (Access 2), and was not tested in Access 1 or 1.1. In earlier versions, the example query must be a little different (needs a source table), but the bug is still present.

Demonstrating the flaw

Compare anything to Null, and the result should be Null. Since Null effectively means Unknown, the result of the comparison is Unknown. To check for yourself, open the Immediate Window (Ctrl+G), and enter:
    ? (4 <> Null)
VBA correctly yields Null, but the query engine in Access gets it wrong.

To see the error in Access 2000 and later:

1. Create a new query. No table is needed.
2. Switch the query to SQL View (SQL View on View menu).
3. Paste in this statement:
       SELECT (4 <> Null) AS Expr1;
4. Run the query.

The query output is a single row, where the value of Expr1 is -1, i.e. True. It should be Null.

The query gives the correct result if you reverse the expression:
    SELECT (Null <> 4) AS Expr1;
Clearly, it is completely confused. The inequality operator is symetrical, i.e. comparing A to B should give the same results as comparing B to A.

The value 4 has no significance. Any field or literal shows the bug.

Where it goes wrong

If you switch the query to design view, the expression is displayed as (4 Is Not Null). Modify the query in design view, that expression becomes part of the SQL statement. It appears that JET is actually interpreting the expression wrongly.

In the query design GUI, it makes sense to replace
    [X] <> Null
with
    [X] Is Not Null
since that is probably what the user intended. But to interpret those expressions as the same at the engine level is just plain wrong.

On its own, the expression ([X] <> Null) is not a very useful piece of SQL. The real-life problems arise in more involved statements such as:
   SELECT COUNT(*) FROM T1,T2
   WHERE (T1.Fn<>T2.Fn) AND (T1.ID=T2.ID);

If the discussion about Nulls is new territory for you, see Common errors with Null.