How to Find Missing Numbers and Ranges in a Microsoft Access Sequence
Question details
The user needs a Microsoft Access query that returns missing numbers and contiguous missing ranges from a source sequence, including values stored as alphanumeric strings such as KFO numbers.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- The user manages a table with sequential identifiers and wants to identify gaps in the numeric sequence for auditing or data cleanup.
- Observed behavior
- The desired result is a query that returns StartOfRange, EndOfRange, and SizeOfRange for each missing range, or a list of individual missing numbers.
Before writing the query, create or identify a Counters table containing sequential integers from 1 through a number greater than the highest numeric portion in the source table. Confirm the exact table and field names, such as [Main Table] and [KFO NO], so you can substitute them correctly.
Find Complete Missing Ranges with a Counters Table and Val() Query
Use this method when you need contiguous missing ranges rather than only individual missing numbers, and when the source values include letters such as KFO numbers.
This approach uses a Counters table of sequential integers and the Val() function to extract the numeric portion of alphanumeric values. It returns StartOfRange, EndOfRange, and SizeOfRange for each gap.
In Microsoft Access, create a table named Counters with a numeric field named Counter. Fill it with sequential integers from 1 through a value larger than the highest numeric portion in your source table.
Go to Create > Query Design, close the Show Table dialog, and switch to SQL View so you can paste the missing-range query.
Paste the query that uses Val([KFO NO]) to compare [Main Table] with Counters. Replace [Main Table] and [KFO NO] with your actual table and field names, then run it to see StartOfRange, EndOfRange, and SizeOfRange.

List Individual Missing Numbers with a VBA RemoveAlpha Function
Use this method when you only need a simple list of missing numbers rather than complete ranges.
Work with Exported Access Data in WPS Office as a Free Microsoft Office Alternative
The missing-number query belongs to Microsoft Access, which WPS Office does not directly repair. Instead, export your Access data to Excel or CSV and use WPS Spreadsheets as a free, lightweight Microsoft Office alternative with familiar menus and seamless Microsoft Excel, Word, and PowerPoint format compatibility.

Frequently Asked Questions
Can Microsoft Access find missing numbers when the values include letters like KFO123?
Yes. Use the Val() function or a custom RemoveAlpha VBA function to extract the numeric portion of alphanumeric values before comparing them in a query.
Do I need to create a Counters table to find missing ranges?
Yes. A Counters table containing sequential integers from 1 through a value greater than the highest source number is the standard way to drive the gap query and identify missing numbers.
Why does the missing-range query show no results even though I have gaps?
Check that the Counters table extends beyond the maximum numeric portion in the source table and that the table and field names in Val([KFO NO]) match your actual database.
Can WPS Office open or query a Microsoft Access database directly?
No, WPS Office does not include a database engine for .accdb files. You can export Access data to Excel or CSV and analyze it in WPS Spreadsheets instead.
How do I list only individual missing numbers instead of ranges?
Use a LEFT JOIN query from Counters to your source table with the RemoveAlpha function, and add a WHERE condition that returns only counters with no matching source sequence number below the maximum source value.




