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.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.
To execute the code, return to Access and click the Search button in the example form. Figure 2 shows the populated list box, which contains an ordered list of missing values from the OrderID field in the Orders table.
 | |
| Figure 2. Set a Reference to DAO: This list contains all the missing values from the OrderID field in the Orders table. |
Using the table and field constants, the code populates a recordset employing an ordered set of values from the field you want to search. A For…Next statement cycles through the records, comparing the values of the last record and the current record in an interesting way. As long as the For statement’s expressions evaluate to different values, the For loops add the current For value to a collection. For instance, if the first two values in the recordset are 1 and 4, the For loop evaluates as follows:
For lngMiss = lngLast + 1 To lngNext - 1 For lngMiss = 1 + 1 To 4 - 1For lngMiss = 2 To 3The For statement’s expressions evaluate to values that represent the first and last missing values between the first two existing values. Consequently, the For…Next loop inserts the values 2 and 3 into a custom collection. The code selects the next record and repeats this process until the code reaches the last record in the recordset.
Before closing the recordset, the code adds one final value to the collection: the next value in the sequence. If you want a list strictly of missing values, comment out this line. Otherwise, it’s an easy way to grab the next value in the sequence if you’re generating values as you go. (The example evaluates an AutoNumber field for the sake of convenience only. In a working situation, you wouldn’t need to generate the next AutoNumber value.)
A second For…Next statement populates the list box control by retrieving values from the collection. Just remember that the list box portion of the code simplifies the example; it isn’t required to actually find missing values. Adapt the code to handle the missing values. You could generate a report or even reuse the values.
Accommodate Missing Values
Finding missing date values requires a few changes in the code, but basically, the process is the same. The variables compensate for the Date/Time data type and the comparison statements use date functions. Remember, this procedure will evaluate dates with time values, but it won’t consider the time values in the comparison. The downloadable demo contains both procedures: the one in Listing 1 and another for finding missing dates.
Finding missing values in a field that contains sequential values isn’t difficult. With a small bit of code, you can generate a list of missing values to reuse, audit, or share with others in some meaningful way.
