How to select records with Blank text field

[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

 

[Guest]

Question withdrawn.
David

 

[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

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

 

[Guest]

Thanks.

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

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