How to select records with Blank text field

D

david

I am trying to select records whose value in a text filed is blank, using
Like ""
and I have also tried using
=""
Neither of the above work. Why?
Yet, strangely enough
Not Like ""
will pick up all records that are not blank in the field.

From my SQL:
Select ...
FROM [Z-Any]
WHERE ((([Z-Any].SITENUMBER) Like ""));

Select ...
FROM [Z-Any]
WHERE ((([Z-Any].SITENUMBER)=""));

David
 
J

John Vinson

I am trying to select records whose value in a text filed is blank, using
Like ""
and I have also tried using
=""
Neither of the above work. Why?
Yet, strangely enough
Not Like ""
will pick up all records that are not blank in the field.

From my SQL:
Select ...
FROM [Z-Any]
WHERE ((([Z-Any].SITENUMBER) Like ""));

Select ...
FROM [Z-Any]
WHERE ((([Z-Any].SITENUMBER)=""));

David
Click to expand...

WHERE [Z-Any].[SITENUMBER] IS NULL

should work. NULL - an empty field - is not equal to the zero length
string character "", or for that matter, to anything else including
NULL!

The special syntax IS NULL is the only way to find these records.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
D

david

Thanks.

John Vinson said:
I am trying to select records whose value in a text filed is blank, using
Like ""
and I have also tried using
=""
Neither of the above work. Why?
Yet, strangely enough
Not Like ""
will pick up all records that are not blank in the field.

From my SQL:
Select ...
FROM [Z-Any]
WHERE ((([Z-Any].SITENUMBER) Like ""));

Select ...
FROM [Z-Any]
WHERE ((([Z-Any].SITENUMBER)=""));

David
Click to expand...

WHERE [Z-Any].[SITENUMBER] IS NULL

should work. NULL - an empty field - is not equal to the zero length
string character "", or for that matter, to anything else including
NULL!

The special syntax IS NULL is the only way to find these records.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Top