Find missing numbers (2003 sp2)

Topic

New Reply

I have a sequential list of several thousand check numbers and I need to identify numbers that are missing from the list due to voiding. How could I do this? other than by building a separate number list and comparing the two.

Reply | Quote

Replies

That’s the only good way I know of to do it on a regular basis. Create an Excell workbook and fill down the number of consecutive numbers you want and save it. Then import that table into Access, and use the right join technique that the wizard creates to find those numbers where there is no check. You can do something with self joins to identify the first number of a sequence of missing numbers, but it isn’t complete, so the right join to a table is the best approach.

Reply | Quote

You could flag check numbers before and/or after a gap using the techniques from the threads starting at post 149,659 and post 151,702 (unfortunately the question in the latter is truncated, but the replies are intact).
If you want to generate a list of missing numbers you’ll have to start with a list of all numbers, or write VBA code togenerate the list.

Reply | Quote

Thank you gentleman. I am going to work at using the threads which Hans suggested and then verify my results with a comparison to a second table as Wendal suggested.

Reply | Quote