Is not null criteria produces blank entries

[Guest]

One of my tables is full of addresses and phone numbers. I am trying to run
a query where only records with phone numbers appear. What I have been using
in the criteria row is the statement "is not null." However, many recrods
are returned in the result set that are null, meaning there is no phone
number in this record. Since many of the entries did not have phone numbers
when thery were entered, I am trying to find only entries that have phone
numbers. The ones without phone numbers will be queried later.

Why am I getting null entries when the "is not null" statement has been used
in the criteria row?

 

[Guest]

hi

Have you got a where clause?

Click on the total icon then where it shows total below the field click on
that and select 'where'

 

[Jeff Boyce]

If you get a query result in which nothing shows in the Address field, does
that mean the "value" is null? I suspect if you check the underlying table,
the Address field has its Allow Zero Length String property set to Yes. So
what you're seeing isn't a Null after you put Is Not Null in the criterion,
what you're seeing is "" (a zero-length string).

You could put
Is Not Null And <> ""
as a criterion and see if that changes your results.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Van T. Dinh]

The Field value may be empty String on only white spaces. Null / empty
String and white spaces look the same to us but empty String and white
spaces are not Null.

 

[MGFoster]

One of my tables is full of addresses and phone numbers. I am trying to run
a query where only records with phone numbers appear. What I have been using
in the criteria row is the statement "is not null." However, many recrods
are returned in the result set that are null, meaning there is no phone
number in this record. Since many of the entries did not have phone numbers
when thery were entered, I am trying to find only entries that have phone
numbers. The ones without phone numbers will be queried later.

Why am I getting null entries when the "is not null" statement has been used
in the criteria row?

Just a guess. The phone number column as the property "Allow Zero
Length" (strings) set to Yes. Some users have entered a space in the
phone number column. What appears to be a NULL value is really an empty
string.