DMW logo
tel 01732 833085
tonbridge · kent · UK

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 2020-06-03 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:

Missing cost 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 expresion field

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

Missing text data query


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:

Missing cost data

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:

Missing asset data

With these results:

Missing 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:

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:

Email Purpose table

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

Missing email query

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 let me know about them.


Your Support for dmw TIPS

If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website, keeping it free of advertising.

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

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

To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —

Invoice

Thanks, in anticipation.

SSL Cerification

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

Probably best not to identify who said this