Filter excluding selection excludes NULL-values

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 ?

Andy
LVL 4
cas1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Good Luck!

Gary
0
cas1Author Commented:
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 ???

Andy
0
bluelizardCommented:
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...


--bluelizard
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SimonLarsenCommented:
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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.