summary report for blank values per field in access

Question

We have a large table of data (several thousand records), and a significant amount of fields is recorded for each record. The design of the table means at present some fields are mandatory (required), and others are not required – which I think was probably a mistake when the table was originally created, as most are really mandatory fields of information.

I need a way to get a single summarised report of how many records in the table have blank values for every field in the table. I can export the table data out to an xlsx file and use the auto filter and filter for blanks, but I wondered what the best way to do this in Access would be? E.g., are there any in built report wizards or is there a sample SQL you can share?

So, it would return something like

Field 1 – 23 blank records

Field 2 – 17 blank records

Field 3 – 15 blank records

Etc.

Answer 1

If you really mean 'have blank values for every field in the table' rather than 'any field in the table', as some of the columns have a Required property of True, to appear to be blank those columns would need to contain a zero length string in the case of a column of text data type, or a zero in the case of a column of a number data type where the lookup wizard has been used in designing the table, and there is no row in the referenced table with a value of zero in the key column (NB this column would probably show a text value in those rows which are not Null or contain a zero).  A column of DateTime data type would need to be Null to be 'blank'.  You can test for columns appearing to be either Null or 'blank' in the above context by calling the NZ function in a query's WHERE clause, e.g.

SELECT *

FROM x

WHERE LEN(NZ(a,"")) = 0

   AND NZ(b,0) = 0

   AND c IS NULL;

where x is the name of the table, a is a column of text data type, b is a column of number data type, and c is a column of DateTime data type.

If you do mean 'any field in the table' change the AND operators to OR operators.

Answer 2

PS: To return a list of how many rows per 'blank' column are in the table you can use a UNION ALL query, e.g.

SELECT "a" AS ColumnName, COUNT(*) AS CountOfBlanks
FROM x
WHERE LEN(NZ(a,"")) = 0
UNION ALL
SELECT "b", COUNT(*)
FROM x
WHERE NZ(b,0) = 0
UNION ALL
SELECT "c", COUNT(*)
FROM x
WHERE c IS NULL;

Answer 3

Hi,

In the property ControlSource of textboxes in a form or report or in a column of a query, whatever fits you better, you can use an expression that counts the empty records per field:

DCount("*","YourTable","Field1 Is Null")

DCount("*","YourTable","Field2 Is Null")

etc.

Servus
Karl

---

Access DevCon - online conference April 18+19
Access Forever
Access News
Access-Entwickler-Konferenz AEK