Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Find missing numbers (2003 sp2)

    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.
    Thanks
    chuck

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Find missing numbers (2003 sp2)

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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find missing numbers (2003 sp2)

    You could flag check numbers before and/or after a gap using the techniques from the threads starting at <post:=149,659>post 149,659</post:> and <post:=151,702>post 151,702</post:> (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.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find missing numbers (2003 sp2)

    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.
    Thanks
    chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •