Your question is a good example of where Boolean logic operators differ from our use of the same terms in day to day English. As the meaning of does not equal "#1" or "#2" is readily apparent in day to day English, you might think we could express this algebraically like this:
x = 3
? x <> 1 OR x <> 2
True
This returns TRUE, which is what we are expecting as 3 is neither 1 nor 2. So far so good. But let's change the value of x to 2:
x = 2
? x <> 1 OR x <> 2
True
It still returns TRUE, but that's not what we expect as we want as we want to know where x = neither 1 nor 2, which is what we mean by the use of OR in plain English. In Boolean logic, however, the use of the OR operator will return TRUE if either of the conditions is true. Now, see what happens if we use the Boolean AND operator:
x = 2
? x <> 1 AND x <> 2
False
So, in your case using AND rather than OR will give you the correct results. However, we could use OR correctly if we express our requirements algebraically like this:
x = 3
? NOT(x = 1 OR x = 2)
True
In this expression both x = 1 and x = 2 return a Boolean FALSE:
x = 3
? NOT(FALSE OR FALSE)
True
i.e. NOT FALSE, which is of course TRUE.
Whereas:
x = 2
? NOT(x = 1 OR x = 2)
False
As in this case one part of the OR operation is TRUE:
x = 2
? NOT(FALSE OR TRUE)
False
i.e. NOT TRUE, which is FALSE.
Finally, the other option of using the IN operator:
x NOT IN(2,3)
can be expanded algebraically:
x = 3
? x <> 2 AND x <> 3
False
x = 1
? x <> 2 AND x <> 3
True
But look what happens if we have a NULL at the relevant column position in list:
x NOT IN(2,3,NULL)
We can expand this to:
x = 1
? x <> 2 AND x <> 3 AND x <> NULL
Null
Rather than the expression returning TRUE as we might expect, it returns NULL. This is because NULL is not a value, but the absence of a value, and consequently cannot equal or not equal anything in an arithmetical expression, not even another NULL. The result will always be NULL. Similarly NULL AND anything will always be NULL, so in this case the expression returns NULL. It's unlikely we'd include a NULL in a value list of course, and a literal NULL in a value list would in fact be ignored in VBA, but if a NOT IN operation is used against a set of rows in a table, if there is a NULL at the relevant column position in any one of the rows, the NOT IN operation will always return NULL. In that context the NOT EXISTS predicate is more reliable.
