Filter excluding selection filters out null values

H

hishi

I have noticed that when I use the Filter Excluding Selection button on a
form it also filters out all records which have a null value for that field
even though the current record and field I have selected doesn't have a null
value. How do I stop this from happening?

Thanks.
-Scott
 
J

John W. Vinson

I have noticed that when I use the Filter Excluding Selection button on a
form it also filters out all records which have a null value for that field
even though the current record and field I have selected doesn't have a null
value. How do I stop this from happening?

Null is a funny beast: it's not equal to anything, and it's also not UNEQUAL
to anything. Any comparison to Null - whether "matching selection" or
"excluding selection" - returns neither true nor false, but NULL. In practice
that means the record won't be retrieved.

Rather than using Filter you could use a Query with a criterion

NOT IN ("A", "B", "C") OR IS NULL

to select everything except A, B and C as values, including those records with
nothing at all in the field.

John W. Vinson [MVP]
 
H

hishi

Thanks John. Looks like I will have to come up with a default value for that
field so it never has a value of Null.

-Scott
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top