how do I create a query to find missing numbers?

B

Berta

Can anyone tell me how to create a query to find missing numbers?

For instance, if I've created numbers 05-1111 through 05-2888, but missed
entering 05-1115, how can I create a query to show me if any 05-1115 is
missing from this group?
 
P

PC Datasheet

Create a query and put the following expression in the first field:
MyNumber:Val(Right([MyNumberField],4))

In Excel, create a column of numbers from 1111 to 2888. Import the numbers
into an Access table with one field named Num.

Create your working query as an unmatched query looking for numbers in the
Access table that are not in the query with the expression. Your working
query will return the numbers that you missed when creating the sequence of
numbers.
 
Top