How to get null values when using Filter Excluding Selection

[Marco Brilo]

Hi,
I have a table with a field calld "Letter", it has letters from A to Z and
many times it's blank. I have noticed that when I am viewing my table (or
even a form for that matter), when I right click in on any letter in the
"Letter" field and chose Filter Excluding Selection I get all the values
except that letter and null values! I was wondering if there is a way filter
out the letter I chose but not the null values. I suppose I could write into
the filter: Null or not like A but I was hoping there some option in MS
Access Version 2003 where I could always have the null values included in the
filtered records.
Thanks
Meherzad

 

[Jeanette Cunningham]

Hi,
I have a table with a field calld "Letter", it has letters from A to Z and
many times it's blank. I have noticed that when I am viewing my table (or
even a form for that matter), when I right click in on any letter in the
"Letter" field and chose Filter Excluding Selection I get all the values
except that letter and null values! I was wondering if there is a way
filter
out the letter I chose but not the null values. I suppose I could write
into
the filter: Null or not like A but I was hoping there some option in MS
Access Version 2003 where I could always have the null values included in
the
filtered records.
Thanks
Meherzad

 

[Jeanette Cunningham]

Hi Marco Brilo
I tried this and got the same result as you.
I suppose you are asking because this is something that you need to do
fairly often and it is a nusiance.
Then I would recommend that you set up a form with a combo for searching and
some code that will let you select a letter and the form will show you all
the records without that letter. Using code gives more flexibility than just
filtering on the table.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[ken]

I agree that a customized solution would be better, but failing that,
filtering excluding selection would work if the column's Required and
AllowZeroLength properties are set to True, and its DefaultValue
property to a zero-length string "". It would be necessary to update
the Nulls to a zero-length strings before setting the Required
property (equivalent of 'NOT NULL' in DDL) to True with:

UPDATE MyTable
SET Letter = ""
WHERE Letter IS NULL;

For the benefit of the OP there is no such thing as a 'Null value';
the key thing about Null is that its not a value but the absence of
one, which is the root cause of the original problem of course because
comparing a to Null to anything results in Null, neither True nor
False.

Ken Sheridan
Stafford, England