Showing records with one or more null fields

[FrustratedAssnt]

We have a really simple database, which is nice. But someone went in and
erased some of the data in some of the fields. There are a lot of records in
there, with a lot of fields. How can I run a single query that will show me
all of the records that have missing data? I don't want to run a query for
every field!

 

[John Spencer]

IF every field is supposed to have data, you could try a totals queyr

SELECT Count(*) as NumRecords
, Count(FieldA) as NumA
, Count(FieldB) as NumB
, Count(FieldC) as NumC
FROM YourTable

That will show you which fields have missing data.

But there is really no simple way to find which records have a null
value in a field,

You could use
SELECT FieldA, FieldB, FieldC
FROM YourTable
WHERE FieldA is Null
OR FieldB is Null
OR FieldC is Null

That will return records where one of the fields is null.

In the query grid, you would add all the fields you want to check and
then put Is Null as criteria for each field. In order to get the OR
effect you would need to put the criteria on a separate criteria line
for each field.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================