Finding missing records.

[Chris Savedge]

We have a table here that uses auto num to track records. Obviously these
numbers should be in sequential order but due to the occasional system
problems there are gaps in the sequence order. I need to find the gaps
(missing numbers) in the sequence. Any help is greatly appreciated.

Thanks,
Chris

 

[Bas Cost Budde]

answered in mpa.modulesdaovba

Next time please crosspost (include multiple newsgroups, if you must, on
the To: line)

 

[Nikos Yannacopoulos]

Chris,

I've done the exact same thing on an invoice header table. Here's my code:

Sub find_missing()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim x As Long
Dim y As Long
Dim miss As DAO.Recordset

On Error Resume Next
DoCmd.DeleteObject acTable, "Missing Invoices"
DoCmd.RunSQL "CREATE TABLE [Missing Invoices] (DocNo long)"
On Error GoTo 0

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT BillDocNo FROM [Invoices] WHERE Date

=#05/01/2003# ORDER BY BillDocNo")

Set miss = db.OpenRecordset("SELECT DocNo FROM [Missing Invoices];")

rst.MoveFirst
x = rst.Fields(0).Value

On Error GoTo Finish
For c = 1 To 50000
rst.MoveNext
y = rst.Fields(0).Value
If y - x > 1 Then
For m = 1 To (y - x - 1)
miss.AddNew
miss.Fields(0) = x + m
miss.Update
Next
End If
x = y
Next
miss.Close

Finish:
rst.Close
set db = Nothing
set rst = Nothing

End Sub

Change table and field names as required.

HTH,
Nikos

 

[[MVP] S.Clark]

Create a table that has every possible value, then perform an Outer join
between the two tables.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-