IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
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.
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:
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.
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
[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.
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Query NULL comparison gives wrong result (bug)'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...