Filter excluding selection filters out null values

[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

 

[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]

 

[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