Why does AND filter also filter NULL

[reclusivemonkey]

Hello Everyone,

Been a while since I needed to post on this forum, but I found something rather disturbing today; either that or I have missed something obvious. I have a large query of financial data, and I filter out certain codes which I don't want to include. The filter is;

<>"BL01" And <>"SS01"

Which works as it should, filtering out all BL01 and SS01. However, it also filters out any NULL values, which is something I would not expect it to do. When I enter NULL as a filter, I get the records I expect. What am I doing wrong here? I am using Access 2003 SP3 on Windows XP Service Pack 2

 

[Brianwarnock]

It is nothing to do with AND I think you will find that any filter that does not ask for Nulls filters them out.

Brian

 

[reclusivemonkey]

It is nothing to do with AND I think you will find that any filter that does not ask for Nulls filters them out.

Sorry; I didn't explain fully. There is more than this one field; when I don't enter any filter, I get the records with NULL values in this field.

 

[Brianwarnock]

I did understand that, if there is no filter at all, or a filter specifically asking for nulls ie Is Null, then the nulls will show, any other situation and the nulls are filtered out. I don't know why it just is I think.

Brian

 

[reclusivemonkey]

I did understand that, if there is no filter at all, or a filter specifically asking for nulls ie Is Null, then the nulls will show, any other situation and the nulls are filtered out. I don't know why it just is I think.

OK, Thanks Brian. I've added And Is Null to the end of the filter and it does indeed pick up the Null values. Seems an odd way of doing things I don't know why I haven't noticed that before.