Null Values Not Showing up in Query Results

[RHONDAK72]

I'm using Access 2003. I have a text field that could contain text or it could be null.

I'm trying the query on all recoreds where the field value is Not Like H*.

When I run the query, it not only excludes records where the field begins with "H" but it also excludes records where the field is null. Does anyone know why this happens? I didn't specify any criteria to exclude records where the field is null.

I guess I need to specify "Not Like H* or Is Null" in order for it to return records where the field is null...

My questions are:
Why does this happen?
Does Access 2007 do the same thing?

Thanks in advance!!

 

[RHONDAK72]

I just did a search in Excel Help and found the following explanation:

"If you use the expression Like "*" or Like "%" when defining query criteria for a field, the query results will include zero-length strings in that field, but not Null values."

Ummm.....why??? Does anyone else think this is extremely stupid?!?

 

[SydneyGeek]

It's how SQL works. I saw a good description a couple of weeks ago...

When you use criteria, the result for any record can be True, False, or Unknown. Unknown is another word for Null as far as SQL is concerned. So, "" is a zero length string. It isn't Null because SQL doesn't regard "" as unknown.

Therefore, to test for Null you use the IsNull function: Not Like "H*" or Is Null
so you can convert the unknown to a yes/no test and see the data.

Denis

 

[james_lankford]

say there's 4 people

John A
John B
John C
John D

John A's middle name is Howard
John B's middle name is Edward
John C doesn't have a middle name
John D hasn't told us his middle name yet

Now I want everyone whose middle name is Not Like H*

What should the query return ?
It should return John B because Edward is not like H*
It should return John C because blank is not like H*

It should not return John A because Howard is like H*
It should not return John D because we don't know what his middle name is. It might be like like H*. It might not be like H*. He may not even have a middle name. We don't know. Its null.
The key thing is "we don't know what his middle name is so we can't compare"