I see that you have a solution using a query (SQL), but since I wrote code to
do the same thing, I will go a head and post it.
I have the code in a standard module... but you could put a button on a form
and paste the code in the click event.
In the code, you must change the table names to your table names. Look for
comment lines like this
'#######################################
The table that has the check numbers I named "tblChecks". There is one place
to change it.
these values are identified, write these values to another/new table in the
The another/new table I named "tblSequences". Change it in two places. This
table's records are deleted each time the code runs
These two tables must have a field named "check_number" of type Number -
Long Integer.
I also created a third table, "tblSeries", that has the starting number,
ending number and length of the series. It is a little easier to see the
series.
Table "tblSeries" has fields:
ID - autonumber
lngSeqStart - number - long
lngSeqEnd - number - long
lngSeqLen - number - long
The records in table "tblSeries" also are deleted and recreated each time
the code runs.
To change the length of the series to look for (ie 7 instead of 5), look for
these lines:
'******************
'set this to min numbers that must be in sequence
MinSequence = 5
'******************
And you must have a reference set to MS DAO 3.6 Object Library.
I tested this a little on a small mdb I made. Note - there is minimal error
checking.
Here is the code:
(Watch for line wrap!!)
'------beg code----------
Public Sub ChkSequence()
On Error GoTo MyBad
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim strSQL As String
Dim MinSequence As Integer
Dim i As Long
Dim k As Long
Dim lngNum1 As Long
Dim lngNum2 As Long
Dim lngSequencesFound As Long
Dim lngSequenceStart As Long
'******************
'set this to min numbers that must be in sequence
MinSequence = 5
'******************
lngSequencesFound = 0
k = 0
i = 0
Set db = CurrentDb
'##############################################
' Change tables names after 'FROM' to your table names
' 1 place tblChecks
' 2 places tblSequences
'
'these tables must have a field names 'check_number'
'
'##############################################
'clear dup table
db.Execute "Delete FROM tblSequences", dbFailOnError
db.Execute "Delete FROM tblSeries", dbFailOnError
'open source table
Set rs = db.OpenRecordset("Select [check_number] FROM tblChecks Order By
[check_number]")
'open copy to table
Set rs1 = db.OpenRecordset("Select [check_number] FROM tblSequences Order
By [check_number]")
'##############################################
'##############################################
rs.MoveFirst
lngNum1 = rs.Fields(0)
lngSequenceStart = lngNum1
rs.MoveNext
Do While Not rs.EOF
lngNum2 = rs.Fields(0)
If lngNum2 = lngNum1 + 1 Then
'number is in sequence
k = k + 1
lngNum1 = lngNum2
Else
'break in sequence
If k >= MinSequence Then
For i = 0 To k - 1
rs1.AddNew
rs1!check_number = lngSequenceStart + i
rs1.Update
Next i
strSQL = "Insert Into tblSeries (lngSeqStart, lngSeqEnd,
lngSeqLen)"
strSQL = strSQL & " Values (" & lngSequenceStart & ", "
strSQL = strSQL & lngSequenceStart + k - 1 & ", "
strSQL = strSQL & k & ")"
db.Execute (strSQL)
lngSequencesFound = lngSequencesFound + 1
End If
lngNum1 = lngNum2
lngSequenceStart = lngNum1
k = 1
End If
rs.MoveNext
Loop
If k >= 5 Then
For i = 0 To k - 1
rs1.AddNew
rs1!check_number = lngSequenceStart + i
rs1.Update
Next i
strSQL = "Insert Into tblSeries (lngSeqStart, lngSeqEnd, lngSeqLen)"
strSQL = strSQL & " Values (" & lngSequenceStart & ", "
strSQL = strSQL & lngSequenceStart + k - 1 & ", "
strSQL = strSQL & k & ")"
db.Execute (strSQL)
lngSequencesFound = lngSequencesFound + 1
End If
MyBad:
If Err.Number > 0 Then
MsgBox Err.Number & " " & Err.Description
End If
rs.Close
rs1.Close
Set rs = Nothing
Set rs1 = Nothing
MsgBox lngSequencesFound & " sequences found."
End Sub
'------end code----------
HTH
