How to select records with Blank text field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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...
 
Back
Top