[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Filter excluding selection excludes NULL-values

Posted on 2004-11-09
Medium Priority
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

tbsgadi earned 100 total points
ID: 12531221
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!


Author Comment

ID: 12531270
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

bluelizard earned 800 total points
ID: 12531524
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...


Assisted Solution

SimonLarsen earned 100 total points
ID: 12539675
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)

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

872 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