NOT IN Returning Different Results Than Expected (1 Viewer)

[EzGoingKev]

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").

 

[CJ_London]

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

 

[EzGoingKev]

I thought NOT IN would only drop what matched the criteria. A null would not match so it would not be drop.

 

[CJ_London]

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

 

[arnelgp]

you can also Add another column (on query design) and filter it:

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