DMW logo
contact@consultdmw.com

How to Locate Records With Missing Data in Access Databases

How, in Microsoft® Access databases, to locate records with fields that have missing or inappropriate values due to user error or oversight.

Last updated on 2021-10-02 by David Wallis.


Introduction

If the information output from a database is to be relied upon, then it’s essential that its data is complete and accurate.

The database developer should have included checks on the accuracy of data at the point of input. However, in my experience, there will be some incomplete or wrong records that have got past the checks.

In this article are suggestions on how to weed out missing, inaccurate or inappropriate records. These, if left undetected and uncorrected, will lead to misreporting of, and incorrect conclusions drawn from, the data.


What Classes as Missing Data

These are circumstances in which we might consider data to be “missing”:

The following sections aim to address each of these circumstances.


Missing Data in Text Fields

These are conditions that count towards missing data in fields to which the Text data type has been applied;

In all three of the instances above cells will appear empty.

To locate Nulls and zero-length strings is straightforward:

Access database query for missing data

The query locates both. Criteria are on different rows establishing an OR condition between them.

Locating records that contain space characters only in a field takes a little more thought. Here’s one solution in which we introduce an expression filed into the query and apply a criterion to that.

To create the expression we need to appreciate the action of a couple of Access functions:

By combining there two functions we have the basis of a test for text that consists of space characters only:

?Len(Trim(" "))

For which the result is zero. We use this formula as the expression field ItemLength in our query:

Zoom pop-up window in Access database

Apply the appropriate criterion, of zero, to complete the query:

Access query criteria for missing text data


Missing Data in Numeric Fields

To test for missing values in a field to which Numeric data type has been applied, you’re criterion is Null, as in this example of a query used to list missing values:

Null as criterion in Access query

Using the logical OR condition, as explained in Logical AND and OR Conditions in Microsoft Access Query Criteria, you can combine searches for values missing from more than one field:

Criteria for missing numeric values in Access query

With these results:

Access data sheet of missing numeric data


Missing Data in Date/Time Fields

As explained in How to Work with Dates and Times in Microsoft Access Databases, Access stores a date as a number. So, for finding missing data in any field to which the Date/Time data type has been applied, you go ahead as you would for missing numbers:

Access query criteria for missing dates


Wrong Values in Text Fields

In the section above we covered missing values in text fields. In this one we consider entries that are wrong, or inappropriate, and therefore should be brought to attention in any data cleansing exercise.

You’d probably agree that an email field into which an address has been input that does not include the @ character needs attention. Let’s consider achieving this using the example of these tables from How to Create a Contacts Database in Microsoft Access:

Relationships between tables for email addresses in databse

Here’s the query that will list missing emails — null and zero-length strings — and those addresses that don’t include an @ character:

Query criteria for missing email addresser in Access datebase

Note the one-to-many link between the two tables has been adapted for purposes of the query to show all records for people in tblPerson, not just for those that have an entry recorded in tblPersonEmail.

Another example, is when the user hadn’t understood the significance of the default value set by the developer, and just let it suffice.


Conclusions

This article addresses some of the issues relating to quality of recorded data. The examples are based on my experience of upgrading databases that have been poorly designed. Had more thought gone into the design, then the possibilities for missing, or wrongly recorded, data could have been minimised.

If you have examples of missing or incorrect values, and you consider including these in this page would make it more useful, then please email me about them.


Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

“Oh! I thought the database would fill that in for me.
It usually does … ”

Probably considerate
not to identify who said this