Get first line of data intervals

[Magnus1982]

It just my mistake. I realase when I used your code that if I will show only first row of intervals the customr will not see full view of data. I need to show where the range is closed.I will not change reuirements. Event if it will be not fair to postępowanie them

[Magnus1982]

............Post them.....

[Micron]

actually, I think I had that wrong. 206 is consecutive to 205 BUT is it the last in a group, or is it just the last of your data? Or both?
In other words, yes or no to getting the last record of the data also?
Just trying to be sure because I modified the code and while I got everything else, I didn't get 206.

[Micron]

OK, doesn't really matter. Easy for you to adjust. Change the Do ... Loop to this

Do While Not rs.EOF 'quit when end of recordset file is reached
        lngTwo = Right(rs.Fields("Sequence"), 4) '...and get 4 characters of next sequence value. Leading zeros are ignored
        'subtract value 1 from value 2. If diff > 1, write complete sequence value to table
        If (lngTwo - lngOne) > 1 Then
            rs.MovePrevious
            CurrentDb.Execute "INSERT INTO tblSequence2 (Sequence2) VALUES ('" & rs.Fields("Sequence") & "')", dbFailOnError
            rs.MoveNext
            CurrentDb.Execute "INSERT INTO tblSequence2 (Sequence2) VALUES ('" & rs.Fields("Sequence") & "')", dbFailOnError
        End If
        lngOne = lngTwo 'now set value 1 to be value to so as to compare to next value
        rs.MoveNext 'move to next record to get next value
    Loop

If you also want the last record of the data, add these 2 lines between the Loop and End If (do not add Loop and End If again)

 Loop
    rs.MoveLast
    CurrentDb.Execute "INSERT INTO tblSequence2 (Sequence2) VALUES ('" & rs.Fields("Sequence") & "')", dbFailOnError
End If

[Magnus1982]

It is look ok. However when I am using code line for last record I have error 3265 - Object wasn't found in this cue

[Micron]

make sure you haven't messed up the name of something. If you can't see the problem, post your new code - I can't see it from here
Are you using a phone with autocorrect? It's "collection" not cue.

[Magnus1982]

Sorry for spelling

Below current code

DoCmd.DeleteObject acTable, "PPA300RAP" ‘ Rmove table with final result
DoCmd.SetWarnings False
DoCmd.OpenQuery "CtempPPA300" ‘ run query which creating table base on order number
DoCmd.SetWarnings True
'''''''''''''''''''''''''''''''''''''''''''''''
Dim rs As DAO.Recordset 'create DAO recordset to load sequence values
Dim lngOne As Long, lngTwo As Long
 
Set rs = CurrentDb.OpenRecordset("SELECT PPA300RAP.tbNumer FROM PPA300RAP") 'populate rs
 
If Not (rs.BOF And rs.EOF) Then 'if TRUE there are no records so just exit IF block
  rs.MoveFirst
  lngOne = Right(rs.Fields("tbNumer"), 4) 'get last 4 characters from 1st record
   
  With CurrentDb
     .Execute "DELETE * FROM PPA300INRAP", dbFailOnError 'flush tblSequence2
     .Execute "INSERT INTO PPA300INRAP (tbNumer2) VALUES ('" & rs.Fields("tbNumer") & "')", dbFailOnError 'add 1st complete sequence value
  End With
   
  rs.MoveNext 'move to next record...
  Do While Not rs.EOF 'quit when end of recordset file is reached
     lngTwo = Right(rs.Fields("tbNumer"), 4) '...and get 4 characters of next sequence value. Leading zeros are ignored
     'subtract value 1 from value 2. If diff > 1, write complete sequence value to table
    If (lngTwo - lngOne) > 1 Then
            rs.MovePrevious
            CurrentDb.Execute "INSERT INTO PPA300INRAP (tbNumer2) VALUES ('" & rs.Fields("tbNumer") & "')", dbFailOnError
            rs.MoveNext
            CurrentDb.Execute "INSERT INTO PPA300INRAP (tbNumer2) VALUES ('" & rs.Fields("tbNumer") & "')", dbFailOnError
        End If
     lngOne = lngTwo 'now set value 1 = value 2 so as to compare to next value after 2
     rs.MoveNext 'move to next record to get next value
  
    
  Loop
    rs.MoveLast
    rs.MovePrevious
    CurrentDb.Execute "INSERT INTO PPA300INRAP (tbTekst2) VALUES ('" & rs.Fields("tbTekst") & "')", dbFailOnError
End If
End Sub

[Magnus1982]

OH! I found diffrent column name in last record row. I changed and is working . Thanks for your patience and help. I can go forward now I need to move final result on raport form. I thinking I can do that alone .

Good lern from you

Onece again thank you

[Micron]

Personally I would not repeatedly delete and recreate the same table. It's generally thought that this promotes db bloat and/or corruption. The Delete sql would clear this table before any new records are appended. In fact, there's no need to delete from a table that you just created.
Glad you got it working anyway.
Good luck.