NOT IN Returning Different Results Than Expected

EzGoingKev

Registered User.
Local time
Today, 15:16
Joined
Nov 8, 2019
Messages
199
I needed to filter some data in a query so I added a field titled "ProductDescription" that has the following data in it:

Turbocharger
Turbocharger Mount
Supercharger
And some empty cells

I used NOT IN ("Turbocharger Mount") and it brought back data where the cell contained Turbocharger and Supercharger but dropped the empty fields.

Why would it drop the empty cells?

ETA - To get the result I wanted I used IS NULL OR NOT IN ("Turbocharger Mount").
 
because the empty fields are probably null - and you can't compare null with anything, not even another null

try ProductDescription NOT IN ("Turbocharger Mount") OR ProductDescription is null
 
I thought NOT IN would only drop what matched the criteria. A null would not match so it would not be drop.
 
to expand on my previous post, null does not compare to anything, it evaluates to null, not true or false which is what is required for your criteria

?null ="abc"
Null
?null <>"abc"
Null
?null like "abc"
Null
?not(null ="abc")
Null
?not(null <>"abc")
Null
?null=null
Null

you need to use the isnull function in VBA or is null in sql
?isnull(null)
True
 
you can also Add another column (on query design) and filter it:

Field: Expr1: ProductDescription & ""
Table:
Sort:
Show:
Criteria: Not In ("Turbocharger Mount")
 

Users who are viewing this thread

Back
Top Bottom