How to count skipped numbers?

[MLH]

A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.

How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?

Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.

[Arno R]

"MLH" <CR**@NorthState.netschreef in bericht news:8d********************************@4ax.com...

>A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.

How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?

Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.

Here is some code to get 'some' missing numbers in a table called TabNumbers.

Dim db as Database, rst as Recordset
Dim i As Integer, lngNr As Long
Dim strmsg As String
Set db = CurrentDb
Set rst = db.OpenRecordset("Select Number from TabNumbers Order by Number")
rst.MoveFirst
i = 0
lngNr = rst!Number - 1
Do Until rst.EOF
If rst!Number <lngNr + 1 Then 'missing number
i = i + 1
strmsg = strmsg & "Number < " & lngNr + 1 & " is missing & vbNewLine
End If
lngNr = rst!Number
rst.MoveNext
If i = 5 Then GoTo ShowString 'change this if you need more results
Loop
ShowString:
rst.Close
set rst=Nothing
MsgBox Trim(strmsg), vbInformation, "1st 5 missing numbers..."

You must be able to adapt this to your needs.

Arno R

['69 Camaro]

Hi, Mike.

How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?

Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range

Use a quick query to count the number of missing records for you, and let
the query prompt you for the range. Try:

PARAMETERS LowRange Long, HighRange Long;
SELECT HighRange - LowRange + 1 - COUNT(*) AS NumRecsMissing
INTO tblCountOfMissingRecs
FROM MyTable;

. . . where LowRange is the lowest number in the range for the AutoNumber
column, HighRange is the highest number in the range for the AutoNumber
column, and MyTable is the name of the table you want to count the missing
records from. The number will be stored in the new table,
tblCountOfMissingRecs.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"MLH" <CR**@NorthState.netwrote in message
news:8d********************************@4ax.com...

>A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.

How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?

Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.

[MLH]

Clever.

[Larry Linson]

Rather than answer, since you got two answers; I'd ask a question in
return -- why?

With autonumbers, you can't go back and fill them in to re-use the numbers;
and it has been discussed many times that the only (repeat _only_) valid use
for AutoNumbers is for use internal to your database (joins, and
relationships).

You _can_ not count on them being consecutive, and you shouldn't display
them to the users -- you'll give "traditional, old-time,
green-eyeshade-and-sleeve-garter accountants" a heart attack because they'll
EXPECT them to be sequential and consecutive even if you caution otherwise.

Larry Linson
Microsoft Access MVP
"MLH" <CR**@NorthState.netwrote in message
news:8d********************************@4ax.com...

>A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.

How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?

Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.

[ARC]

Hi MLH, here's a complete function I created to find missing numbers and it
kicks the results out to notepad. You give it the starting number to search,
and it looks for missing integers until it gets to the end of the table.

------------------
Public Function MissingInvoices()
Dim i As Long, rs As Recordset, db As Database, listinv As String, endnum As
Long, strsql As String, numerrors As Long
If Not IsNumeric(Forms!mainmenu!SubMenu.Form!txtStartNum) Then
MsgBox "Please enter a valid number for the starting invoice search
number.", vbCritical
Forms!mainmenu!SubMenu.Form!txtStartNum.SetFocus
End If
Set db = CurrentDb()
Call SetMessage("Opening recordset...")
strsql = "SELECT Invoices.InvoiceNo From Invoices ORDER BY
Invoices.InvoiceNo;"
Set rs = db.OpenRecordset(strsql, DB_OPEN_SNAPSHOT)
numerrors = 0
DoCmd.Hourglass -1
Open GetLocalPath() & "MissingLog.txt" For Output As #1 'create DbLoc.txt
Write #1, "Missing Invoice Sequence Numbers LOG - " & Format(Date,
Forms!frmOpt.Form!DateStyle)
Write #1,
If Not rs.BOF Then
rs.MoveFirst
i = Forms!mainmenu!SubMenu.Form!txtStartNum
rs.MoveLast
endnum = rs!InvoiceNo
Do Until i = endnum
i = i + 1
Call SetMessage("Checking for Invoice#" & i)
rs.FindFirst "InvoiceNo = " & i
'*** Sometime update this rountine to move sequentially through, and
not do the findfirst
'would be much much faster with large invoice db's
'***
If rs.NoMatch Then
numerrors = numerrors + 1
Write #1, i
End If
Loop
End If
rs.Close
db.Close
Call SetMessage("Off")
Close #1
DoCmd.Hourglass 0
If numerrors 0 Then
Dim filena As String, Returnvalue
filena = GetLocalPath() & "MissingLog.txt"
'MsgBox filena
'Returnvalue = Shell("Notepad.EXE " & Chr(34) & filena & Chr(34), 1) '
Run Microsoft Excel.
'RetVal = Shell("WinHlp32.EXE " & filena, 1)
'AppActivate Returnvalue ' Activate Microsoft
ShellExecute 0, "open", Chr(34) & filena & Chr(34), 0, 0, SW_SHOWNORMAL
End If
End Function

[CDMAPoster]

On May 8, 6:50 am, MLH <[e-mail]:

A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.

How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?

Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.

Here's an alternate method that uses subqueries:

http://groups.google.com/group/micro...4a5fee9ff13437

Adapting it to obtain the missing ranges directly from your table
should be straightforward, but a separate table allows you to add a
record numbered 1001 to get the final missing range, if present. To
get the total number of deleted records, subtract the values from both
sides of ' to ', add 1 and sum that expression.

James A. Fortune
CD********@FortuneJames.com

[MLH]

All good points, Larry. I chose to use autonumber in
a mailing list for identifying mailers that were returned
non-deliverable. I don't use them for invoice numbers.
There is no particular relevance to them at all except
for the fact that they serve as quick 'n dirty identifiers.
And, most importantly, they're short.

When a postcard mailer comes back (and when you
send out 14000, many do) they are often covered with
a yellow USPS label with information on it - including a
bar-code for re-routing back to the originator. Often, it
covers up the target address - even the name of the
company. Placing a short 5-digit number on the card
(in several locations) makes it likely that my processor
(me - and on a good day, maybe the wife & kids) will
pick an identifier off really quickly, assisting in deletion
of the obsolete record. As for the choice between marking
for obsolecence or actually deleting the record, well it
makes no real difference to me when a record disappears
completely. If its no good - its no good. Until someone
suggests a beneficial use for housing data on defunct
businesses and bad addresses to me, I'll probably con-
tinue to delete the records. I mean, I wouldn't even add
them to my mailing list if I knew they were bad in the
first place. It cost's me four grand every time I send out
a mailing. Benjamin Franklin said 39-cents saved is 39
cents earned, although that probably happened after
his shocking experience with the kite and all.

[DavidB]

On May 8, 7:50 am, MLH <[e-mail]:

A mailing list table in its virgin state contained sequential,
consecutive integers in an autonumber field (A97). I've deleted
records throughout the table. Now I would like to identify each
skipped number. Hmmm??? If I'd had a boolean field named
[Trash], I could-a-check-marked it and they would-a-been much
easier to count.

How could I determine that 3 numbers were missing from the
following sequence: 1 2 4 5 7 9 and record those missing
numbers somewhere (array, table, other file)?

Now look, I know if originally there were a thousand records numbered
from 1 to 1000, that I'll have to tell the procedure the range - just
in case the last 3 records (998, 999 & 1000) have been deleted. But I
haven't a clue what to do after that. The same goes for the bottom end
if records 1, 2 and 3 have been deleted.

Whats the objective in finding the 'missing' numbers? It looks like
you are misusing an autonumber field. Refer to Devs 10 commandments.

[MLH]

Think of it, David, as an exercise. What's the point of walking a mile
on a treadmill when you don't get anywhere might be a fair question.
I could easily wanna do the same thing in an incrementing long integer
field because my boss says "I wanna know which damned records
were deleted and who the hell deleted them"

>
Whats the objective in finding the 'missing' numbers? It looks like
you are misusing an autonumber field. Refer to Devs 10 commandments.