Select Query; Criteria; Null Values

[syoung7903]

I have a very simple select query. I am selecting records from a table where the criteria for field A is NOT ABC . The results do exclude all the records whose field A is ABC....but it also excludes all records where field A is null. Why is that? Null is not ABC. This is very confusing to me.

 

[Jon K]

The results do exclude all the records whose field A is ABC....but it also excludes all records where field A is null. Why is that? Null is not ABC. This is very confusing to me.

Null is null. It doesn't equal to anything.

You can put Is Null below your criteria. That is, put it in the or: row.
.

 

[syoung7903]

I do understand that. But if I leave the criteria "Not ABC" out the records with a null value are returned so why when I put the criteria back in are the records with a null value excluded. Am I making any sense at all?

 

[Jon K]

When you leave out "Not ABC" in the criteria, you are not using any criteria, so every record, including the null values, will be returned.


But when you put "Not ABC" in the criteria, you are asking Access to return only those records where FieldA equals to "Not ABC". Since Null does NOT equal to anything, it is therefore NOT equal to "Not ABC"
.

 

[syoung7903]

Thank you. That makes me very scared. I have been using Access for several years and I just wonder how many times my misunderstanding of this has caused me to make errors. WOW.

Thanks a lot!