NOT IN Returning Different Results Than Expected (1 Viewer)

EzGoingKev

Registered User.
Local time
Today, 18:47
Joined
Nov 8, 2019
Messages
69
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

Super Moderator
Staff member
Local time
Today, 23:47
Joined
Feb 19, 2013
Messages
12,895
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

Registered User.
Local time
Today, 18:47
Joined
Nov 8, 2019
Messages
69
I thought NOT IN would only drop what matched the criteria. A null would not match so it would not be drop.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:47
Joined
Feb 19, 2013
Messages
12,895
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

error reading drive A:
Local time
Tomorrow, 07:47
Joined
May 7, 2009
Messages
11,446
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

Top Bottom