Find and Generate Missing Values in an Access Table

Find and Generate Missing Values in an Access Table

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 3

The 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.

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as