Jegajothy vythilingam
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.
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
~bp
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.
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
select RecordNumber
from tblDummy D
left join tblJasonJustin TN On D.RecordNumber =TN.Numb
where TN.Numb is null
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
~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
~bp
ASKER
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
~bp
https://www.experts-exchange.com/questions/28678098/find-missing-sequence-numbers-access-2003-query.html
.