Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    149
    Thanks
    0
    Thanked 1 Time in 1 Post

    Filter Excluding Selection excludes nulls too (200

    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 <img src=/S/flags/Scotland.gif border=0 alt=Scotland width=30 height=18>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Filter Excluding Selection excludes nulls too (200

    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
    <code>
    <>"Passed"
    </code>
    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
    <code>
    <>"Passed" Or Is Null
    </code>
    which can only be done using Filter By Form or Advanced Filter (or in a query, of course).

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    149
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter Excluding Selection excludes nulls too

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Filter Excluding Selection excludes nulls too

    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.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Excluding Selection excludes nulls too

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •