Query not retrieving null fields

[Guest]

Hi,

I have a simple query in access.
Here is the SQL:
SELECT Table1.[Field]
FROM Table1
WHERE ((Not (Table1.[Field])="Y"));

In the field column, there are only rows of Y and null fields.
I want to select only the null fields, but when choosing the criteria not
"Y", it does not return any rows.
The datatype is Text and field size is 1.
I have tried using a different field size and it still doesn't work.
If I choose to select Null fields then the query works.

Any idea why it doesn't work if I choose not Y in the criteria (therefore
wanting to list null fields by default).

Hope this makes sense.

Thanks for your help.

 

[Brendan Reynolds]

Your expression will return rows where the result of the expression is True.
But the result of a comparison with Null is Null. Null represents an unknown
value. Is an unknown value equal to or not equal to 'Y' (or any other known
or unknown value)? The answer is unknown, i.e. Null.

To select only rows with Null values in the field, change the criteria to
....

WHERE Table1.Field Is Null

 

[Allen Browne]

Try the WHERE clause of:
WHERE (Table1.[Field] Is Null) OR (Table1.[Field] <> "Y");

For an explanation, see the first part of this article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html