Getting All Records where Particular Column is empty in Access

Question

I am writing a query in Access where I have to get all the records where a particular column is empty, how I can do this?

This is what I am thinking it should be, but its not working.

SELECT *
FROM TABLE
WHERE PARTICULARCOLUMN = ''

Renata

Have you tried Is Null? It is the best solution – Acupuncture

possible duplicate of Only return rows that have an empty field? – Rabid

Answer 1

This would handle both empty strings ('') and NULL values in the column.

SELECT *
FROM TABLE
WHERE Nz(PARTICULARFIELD,'') = ''

Cesya

Doesn't this set the value of the where clause to be " WHERE '' = '' " whenever the PARTICULARFIELD is null? – Expensive

I see... slight misinterpretation of the question... Thanks – Expensive

It is most unlikely, but not impossible, that you have zero-length strings, Null is far more likely. The statement above is not sargable and will be slow to run on large tables. – Acupuncture

Answer 2

Try...

WHERE PARTICULARFIELD Is Null 

Sample from Web:

SELECT [Table1].[CustomerID], [Table2].[CustomerID]
FROM [Table1] LEFT JOIN [Table2] ON [Table1].[CustomerID] = [Table2].[CustomerID]
WHERE ((([Table 2].[CustomerID]) Is Null));

See: http://www.fabalou.com/access/Queries/isnullquery.asp

Expensive

You do not need all those parentheses in the WHERE statement, but +1 for Is Null. – Acupuncture

@Renous: last time this came up (#4652217) you asserted that zero-length strings and space-filled strings should be handled. – Rabid

Answer 3

In my case, I wanted to find all records with a particular field empty. Not NULL didn't work. This did: WHERE ((Not (table.field)="").

So, to find both the empties and the nulls, just to be sure...

WHERE (((table.field)="" Or (table.field) Is Null)

Grower