Blank field don't "act" like they are blank

K

Kris

Hi all,
I have created a query in which I want to look at all records with a
certain field that is blank. However, my search results are missing
some records. They are records that used to have data them but now
they are blank. Does Access leave something behind in the field that
causes the query to not include these records in the results?
Thanks for any help.
Kris
 
K

Ken Snell [MVP]

Completely depends upon how they became "blank". There are two kinds of
"blank" possibilities in a database: a Null value, and an empty string.
They are not the same. If the field is a text field, it's possible that it
contains an empty string, not a Null, and thus, if your query is looking for
Is Null, these records won't be selected.

Best to use a criterion expression like this:
Len([MyFieldName] & "") = 0

This will be true if the field is "blank" because it contains a Null or it
contains an empty string.
 
Top