Extract Fisrt/Last row

[kirkm]

I want the first and last entries for Location from a recordset depending on field "Key" (which is passed in).

My plan was to move backwards until Key Changes then likewise forward to get the last.

Key = rz!Location
    Do
        rz.MovePrevious
        'Debug.Print rz!MyCount
    Loop Until (rz.BOF) Or (rz!Location <> Key)
    rz.MoveNext
    theStart = rz!MyCount
    Do
        rz.MoveNext
        'Debug.Print rz!MyCount
    Loop Until (rz.EOF) Or (rz!Location <> Key)
    rz.MovePrevious
    theEnd = rz!MyCount

The problem is EOF and BOF. If they're true I've lost rz!MyCount. Have tried some things, like Do While and Exit Do but get the same result. How might this be handled properly? Thanks.

 

[arnelgp]

do you have autonumber of PK on your table?

Key = rz!Location
Dim rx As DAO.RecordSet
Set rx = Currentdb.OpenRecordSet("Select MyCount From yourTable Where Location = '" & Key & "'")
theStart = Null
theEnd = Null
With rx
    If Not (.Bof And .Eof) Then
        .MoveLast
        theEnd=!MyCount
        .MoveFirst
        theStart=!MyCount
    End If
    .Close
End With

 

[kirkm]

Hi Arne. No, no autonumber. The problem is 'Key' may be duplicated... and I only want the first-last MyCount that is squential and includes the number passed in.

 

[theDBguy]

Hi Arne. No, no autonumber. The problem is 'Key' may be duplicated... and I only want the first-last MyCount that is squential and includes the number passed in.

Hi. Can you post a sample set of data and tell us which one you consider first and which one is last?

 

[arnelgp]

so just sort MyCount:

Key = rz!Location
Dim rx As DAO.RecordSet
Set rx = Currentdb.OpenRecordSet("Select MyCount From yourTable Where Location = '" & Key & "' Order By MyCount;")
theStart = Null
theEnd = Null
With rx
    If Not (.Bof And .Eof) Then
        .MoveLast
        theEnd=!MyCount
        .MoveFirst
        theStart=!MyCount
    End If
    .Close
End With

 

[Pat Hartman]

First and Last have no meaning unless you have a unique identifier on which to sort. In a relational database tables and queries are unordered sets so without order, first/last are meaningless.