Query with "Is Not Null" Criteria not Working

Question

I have a table that has Person's Names, Companies, and Phone Numbers.  Some of the entries have all those fields entered, and some of the entries only have a Company name and Phone number with the person's name blank.  I am trying to run a query that will give me only the entries where the Person's name is not blank.  In the criteria under the Peron's Name field, I entered "Is Not Null" (entered without quotes).  When I open the query, I still get the entries that have blanks under the Person's name field.

When I try it the other way, and ask for all the entries that are null ("Is Null"), it sorts them out and I get all of the entries under the Person's name that are blank.  That tells me that Access recognizes that the entries are null, Why won't it exclude them when I ask for the ones which are not Null?

Here is the SQL version of my query:

SELECT Phone_List.Name_Full, Phone_List.Agency_Company, Phone_List.Phone_Work

FROM Phone_List

WHERE (((Phone_List.Name_Full) Is Not Null));

Any Help Would be Appreciated,

Balfour211

Answer 1

My guess is that you had somehow set the database so that this field's Allow Zero Length string property (normally set to No) was set to Yes, and that you had "" - an empty string - in the field. This is not NULL and will fail these tests!

NULL means "this value is undefined, unknown, unspecified". A zero length string isn't really NULL. For instance, it would be legitimate to allow zero length strings for a person's MiddleName field; "" in the field would mean that the person in fact has no middle name, whereas NULL would mean that you don't know whether they do or not.

A safe criterion that catches either would be

Len([Name_Full] & "")=0

Answer 2

To update my question, I ran some test queries, and I found that When I did the opposite and asked for all the Null entries, I did not get all of them.  I had to add an "or" to the criteria and ask for all Null or "" entries.  This gave me all of the blanks, but when I try and ask the opposite, I still get blanks in my response.

SELECT Phone_List.Name_Full, Phone_List.Agency_Company, Phone_List.Phone_Work

FROM Phone_List

WHERE (((Phone_List.Name_Full) Is Not Null)) OR ((Not (Phone_List.Name_Full)=""));

Balfour211

Answer 3

NEVER MIND.

The data that I was working with came from a query that I exported to Excel.  I then imported it back in using the excel spreadsheet that I created with the export.  I took the same data, and cut out the export aspect of the data transfer.  In essense, creating clean data on an Excel spreadsheet and Imported that data.  The queries worked fine then.  The export must have altered the data somehow. 

Balfour211

Answer 4

It all came down to the way I exported the query.  I see where I chose to include some formatting.  That is what caused my problems.  I exported the same query with taking that option, and all worked the way I thought it should.  Thanks for taking the time to help me. 

I will keep the criteria that you suggested in mind for my future projects.

Thanks,

Balfour211