Filter excluding selection filters out null values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hishi - 23 May 2007 18:52 GMT

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

Reply to this Message

John W. Vinson - 23 May 2007 22:10 GMT

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

Reply to this Message

hishi - 24 May 2007 00:05 GMT

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

> >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
[quoted text clipped - 14 lines]
>
> John W. Vinson [MVP]

Reply to this Message