Find First number in sequence of numbers

[losstww]

Access 2010, Windows 7



I have a table with a column named CheckNumbers. I need to find the first number in a sequence of numbers. Example:

123456
123457
123459
123462
157936
157937
157938

The database has thousands of records.

Any help would be appreciated!

[June7]

This is difficult even if the 'sequence' didn't allow gaps as shown in the sample data. Numbers are skipped between 123459 and 123462 yet 123462 does not begin a new 'sequence'. Which digit determines beginning of new 'sequence'? By this example appears to be second position. Consistency in criteria is key to accomplishing this.

[losstww]

The database is a list of fraudulent checks. Any consistency in check numbers implies that a particular person or group is generating the fraudulent checks. The first Check number in a sequence of numbers will be bumped up against a legitimate check number to determine if the originator was/is a possible employee. It has worked well manually but an automated process would save time. I am currently working on a function. I will post when I figure it out.

Thanks for the response.

[June7]

What is the logic applied in the manual process? Can you represent it in a flow diagram?

[losstww]

I found a work around to get the first number in a sequence of numbers.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDupNum As Variant
Dim strSaveNum As Variant
Dim tempvar As Variant
Dim ct As Integer
Set db = CurrentDb

'Delete Temp Table if it already exists
DeleteTable ("tbl_SequenceNumbers")

'Create temp table to store sequence check numbers
db.Execute "CREATE TABLE tbl_SequenceNumbers(check_num INTEGER)"

'Open recordset sorting by check number
Set rs = db.OpenRecordset("Select t.[CheckNumber] FROM [Tbl_BadChecks] As t WHERE t.[CheckDate] Between #7/1/2012# AND #11/1/2012# ORDER BY t.[CheckNumber] ASC")

'get the record count
ct = DCount("[CheckNumber]", "[Tbl_BadChecks]")

'reiterate through the recordset pulling the first number in a sequence of numbers

rs.MoveFirst
Do Until rs.EOF
strSaveNum = rs![CheckNumber]
If tempvar = 0 Then
tempvar = rs![CheckNumber]
End If

If rs.AbsolutePosition = ct - 1 Then
db.Execute "INSERT INTO tbl_SequenceNumbers(check_num) VALUES (" & tempvar & ")"
tempvar = 0
Else

If (tempvar + 100) > strSaveNum Then

Else
db.Execute "INSERT INTO tbl_SequenceNumbers(check_num) VALUES (" & tempvar & ")"
tempvar = strSaveNum
End If
End If

rs.MoveNext
Loop

I made an assumption that if a sequence of numbers is separated by a count of 100 then a new sequence should begin.