By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,521 Members | 1,196 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,521 IT Pros & Developers. It's quick & easy.

Report on Missing Records in a Sequence

P: 1
Our database tracks batches which have a Batch Number (ID). I'd like to know if there is a way to report on or return ID(s) that are missing from the sequence. Sometimes a user forgets to enter a batch and it would be nice to see which number is missing. I realize we can't report on a record that isn't there. But I was wondering if there is code that will allow us to see a break in the sequence of batch numbers?
Thank you.
May 16 '14 #1
Share this Question
Share on Google+
1 Reply


Seth Schrock
Expert 2.5K+
P: 2,951
Depending on how many records you are returning, this could get expensive in terms of computer resources and take awhile to complete since you would have to loop through each record.

Because of the time it could take, I think that it would be best to first test if a number is missing and then if there is, check which one.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim strRst As String
  4. Dim c As Integer 'If number of records is over 32,767, then us Long
  5. Dim s As Long
  6. Dim e As Long
  7.  
  8.  
  9. strRst = "SELECT ... ORDER BY ID"
  10. Set db = CurrentDb
  11. Set rst = db.OpenRecordset(strRst, dbOpenDynaset)
  12.  
  13. With rst
  14.     .MoveLast
  15.     e = !ID
  16.     .MoveFirst
  17.     s = !ID
  18.  
  19.     If e <> (s + .RecordCount - 1) Then
  20.         For c = s to e
  21.             If c <> !ID Then Debug.Print c
  22.         Next c
  23.     End If
  24.  
  25.     .Close
  26. End With
  27.  
  28. Set db = Nothing
  29. Set rst = Nothing
May 16 '14 #2

Post your reply

Sign in to post your reply or Sign up for a free account.