Find numbers missing in sequence

[tsm1993]

I have a table that I automatically assign certification numbers based on those assigned (for example 1240000-1241500). Occasionally a number will get skipped or deleted and I need to be able to find those numbers that are skipped. The only field I need to look at are tblIndCertification.Cert#. I am sure this is easy, but I have stumped myself (probably by overthinking). Can someone please help me either through query or code find a way to print a list of those numbers skipped.

Thanks a ton.

 

[HarleyQuinn]

For a query you could just compare the field to a table containing all of the numbers in the range and see which don't appear.

Hope this helps

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[genomon]

You could loop thru a recordset and check for value = old value + 1. If not equal, print new value and increment old value by 1 for the next iteration.

Cogito eggo sum – I think, therefore I am a waffle.

 

[MajP]

As Genomon stated. Something like.

Public Sub findMissingSequence(expr As String, domain As String)
  Dim maxVal As Integer
  Dim minVal As Integer
  Dim rs As DAO.Recordset
  maxVal = DMax(expr, domain)
  minVal = DMin(expr, domain)
  
  Set rs = CurrentDb.OpenRecordset(domain, dbOpenDynaset)
  Do While minVal < maxVal - 1
    rs.FindFirst expr & " = " & minVal + 1
    If rs.NoMatch Then
      Debug.Print minVal + 1
    End If
     minVal = minVal + 1
  Loop
End Sub

Public Sub test()
  findMissingSequence "intValue", "tblSequence"
End Sub