Link to home
Start Free TrialLog in
Avatar of Jegajothy vythilingam
Jegajothy vythilingamFlag for United States of America

asked on

Access 2016 - finding skipped numbers

My OS is win 10 pro 64 bit and i have office 355 and Access 2016.  I have a small table where the field is an Integer, and in the table it is num numerically.  how do I find out if a number was skipped in that field, by what method ?  thank u.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Avatar of Bill Prew
Bill Prew

Here's a quick and dirty basis you could use, or build upon.  It uses a subroutine to find the missing numbers, and just writes them to the Immediate window in the debugger.

Sub Missing()
    For i = DMin("Key", "Table1") To DMax("Key", "Table1")
        If Nz(DLookup("Key", "Table1", "Key = " & i), 0) = 0 Then
            Debug.Print i
        End If
    Next
End Sub

Open in new window

~bp
Avatar of Jegajothy vythilingam

ASKER

Hi Rey,
I saw your solution.  Can u please help me with the sql syntax.  My table name is tblJasonJustin and the field name where the integer number are is : Numb.
Thus how would I do this query SQL statement
from  tblDummy D
left join TableName  TN On D.RecordNumber =TN.SequenceNumber
where TN.SequenceNumber is null

thank u for your help.
test this

select  RecordNumber
 from  tblDummy D
 left join tblJasonJustin  TN On D.RecordNumber =TN.Numb
 where TN.Numb is null
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In response to Bill, can u please suggest some code to show that there are no  skipped numbers in the above code with a message box or something.  thank u.
Ray, how about marrying the 2 approaches and saving having to create the query.  So we create the dummy table, and using a modified SUB like you had (below) we just insert the missing sequence numbers right in there?

Sub FindMissingSequence()
    Dim rs As DAO.Recordset, minNum As Long, maxNum As Long, j As Long
    minNum = DMin("SequenceNumber", "TableName")
    maxNum = DMax("SequenceNumber", "TableName")

    For j = minNum To maxNum
        If Nz(DLookup("SequenceNumber", "TableName", "Key = " & j), 0) = 0 Then
            CurrentDb.Execute "insert into tblDummy(RecordNumber) values(" & j & ")"
        End If
    Next

End Sub

Open in new window

~bp
that will be cool Bill
Sure, here is a version that shows a message box summary at the end:

Sub Missing()
    MissingCount = 0
    MissingList = ""
    For i = DMin("Numb", "tblJasonJustin") To DMax("Numb", "tblJasonJustin")
        If Nz(DLookup("Numb", "tblJasonJustin", "Key = " & i), 0) = 0 Then
            MissingCount = MissingCount + 1
            If MissingList = "" Then
                MissingList = i
            Else
                MissingList = MissingList & "," & i
            End If
        End If
    Next
    MsgBox "Missing Count = " & MissingCount & vbCrLf & "Missing List = " & MissingList
End Sub

Open in new window

~bp
Rey' s solution worked, thank u and well done. gracias.
For what it's worth, if you think there are only going to be a few missing records then I'd go with my last approach with a SUB that just puts up a MSGBOX.  If you think there could be many many missing, then the default table approach where it will hold all the missing items would make more sense, since the MSGBOX could get out of hand with too many numbers.

~bp