Filter excluding selection excludes NULL-values

Posted on 2004-11-09
Last Modified: 2011-10-03
I use the Filter excluding selection from the standard access contextmenue. If the user selects all excluding a certain value, all NULL-values are deselected as well. I know, that when I use the NZ function in the query, the filter is set properly. But for this all queries of all forms has to be adapted. Is there an easy way to fix this problem ?

Question by:cas1
    LVL 46

    Assisted Solution

    Sorry that's the default.
    You've got 2 ways to go
    1) Create your own button in the menu that does filter excluding but leaves nulls
    2) Use NZ as you say

    Good Luck!

    LVL 4

    Author Comment

    Using the NZ will slow down the query. it is already performance sensitive so I dont it want to slow down anymore.

    Whats the reason for this behavior ? Why is NULL excluded when I want to exclude another value ? That makes no sense for me ???

    LVL 14

    Accepted Solution

    this won't be of any help, it just tries to answer the question about the sense of NULL-behaviour...

    the philosophy behind NULL is that it's "undefined": the value of the field is in fact *not* NULL, but NULL is simply used to show that the value is in fact not defined, and thus it's supposed to be "unhandlable".  the logical consequence is something like: "no matter what you test it against, the test always fails" (there's only a limited set of functions that can handle NULL, e.g., ISNULL or NZ).  so, if you look at a table without any filters, no test are done, and all records will be shown.  but as soon as you run any kind of test on it, in this case it's "value <> 'sometext'", not only will all records that have the string "sometext" fail the test (and thus not be displayed), but also all records that have NULL will fail the test (based on the philospohy mentioned above) and will therefore not be displayed, either.  it's the database's way of saying "i don't know it, so it certainly can't pass any tests".

    coming from the "programming world" myself, where NULL doesn't really exist (or at least: where NULL used to not exist back in the old days), i, too, sometimes find the NULL behaviour found in the "database world" very annoying...

    LVL 8

    Assisted Solution

    I don't know if this is applicable for you but you can turn off ANSI nulls for many ODBC connections (it is a property on the last page of the ODBC set up for MS SQL for example).

    Turning off ANSI nulls should rectify this behaviour for you.

    If it is a local table I guess it's time to go looking for a way to turn off ANSI nulls in access (I don't even know if that is possible)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now