RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Find and Generate Missing Values in an Access Table

Access can offer a lot of help with missing values, but finding and generating missing values in a field of sequential values requires a bit of code. Find it here.

equential values can represent a lot in a database. Sometimes they uniquely identify records (as primary key values). Often they're part of the natural data, such as a purchase order number. In any case, a field of sequential values can be vulnerable to problems if a value goes missing, which can happen when you delete a record or a data entry operator simply skips the next value in the sequence. Even if missing values don't create a problem, you might need to account for them when conducting audits.

Now, Access can offer a lot of help with missing values, but finding and generating missing values in a field of sequential values requires a bit of code. The code in this article shows you how to generate a list of missing values. How you use those values is up to you.

A Missing-Values Example
The first step to finding missing values in a table is to determine exactly what you mean by missing. Within the context of the technique described here, missing refers to values not present in a sequential list. These can be numeric values or date values, but not string values. Because string values require unique customization, this technique doesn't extend to text. In addition, although date values can include time components, this technique evaluates only the date part.

For demonstration purposes, the technique displays the missing values in a list box. Specifically, the code works against the Orders table in the Northwind database (the sample database that comes with Access). To create this form in Northwind, simply create a blank form and insert a list box and a command button. Then, refer to Table 1 below to set the properties.

Figure 1. Set a Reference to DAO: You need to reference the Data Access Objects library.

Object Property Setting
form Caption Missing Numbers
Scroll Bars Neither
Record Selectors No
Navigation Buttons No
list box Name lstMissing
Row Source Type Value List
Column Count 1
command button Name btnSearch
Caption Search
Table 1. Control Properties

Next, open the form's module by clicking the Code button and enter the code in Listing 1. You can drop the form (or code) into just about any Access database. Be sure to update the Const statements that identify the table and field that the procedure evaluates.

The code uses the Data Access Objects library, so you need to reference that library. Choose References from the Tools menu (still in the VBE), check the Microsoft DAO 3.x Object Library (as shown in Figure 1), and click OK.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date