Filter Excluding Selection excludes nulls too (200

Topic

New Reply

Hi,
I’ve just noticed a behaviour that I wasn’t aware of:

I have a table that has a column that contains mostly nulls, sometimes it has “Passed” and sometimes it has other content “To be checked” etc etc.

If I right-click on a field that has “Passed” and choose “Filter Excluding Selection”, it excludes all the “Passed” records but also excludes all the nulls.

Is this normal/deliberate behaviour?

Usually my data has very few nulls & I have used the right-click method to produce stats on many many occasions, have they all been wrong????

Thanks in anticipation,

Jim MacLeod
Shetland Isles

Reply | Quote

Replies

It’s “normal” behavior. If you apply “Filter Excluding Selection” (with “Passed” as example), then activate Filter by Form or Advanced Filter, you’ll see that the Criteria line for the relevant field has been set to

"Passed"

Null values are never included by such a condition, because Null is not a value, it’s the absence of a value. Null is neither equal to “Passed” nor unequal to “Passed”, it’s not there. If you want to include Nulls, you must change the Criteria to

"Passed" Or Is Null

which can only be done using Filter By Form or Advanced Filter (or in a query, of course).

Reply | Quote

Thank you Hans, I guess all my quick stats in the past have been wrong then!

Oops…… now, do I tell my boss or not…….

Reply | Quote

You wrote that your data have/had very few nulls so the results probably haven’t been very different from what they should have been.
What’s the impact of your statistics? If they’re used for a general overview of activities, the small errors won’t have mattered. But if they may have influenced decisions involving individual persons it’s probably better to inform your boss and discuss the implications.

Reply | Quote

Yeah, this is the null “gotcha,” and believe me, others have ended up in the same boat. My commiseration!
Pat

Reply | Quote