Trying to get the next to the last record field in query designer

I'm using DLast to attempt to get a field into a query with :

Previous Running Note: DLast("[RunningNote]","tblMemberRunningNotes","[MemberID] = [MemberID]")-1

But that isn't working.  What I want is not the last RunningNote but rather the next to the last one.

--Steve
SteveL13Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
I would use a recordset:

lngMemberID = <the member id to look up>
strSQL = "Select Top 2 [RunningNote] " & _
    "From tblMemberRunningNotes " & _
    "Where [MemberID] = " & lngMemberID & " " & _
    "Order By [YourNoteDateField] Desc"

Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount >= 2 Then
    rst.MoveNext
    strNote = rst!RunningNote
End If

/gustav
0
 
PatHartmanConnect With a Mentor Commented:
Last is a nebulous concept since it relies on Order and even if you include an Order By clause in your query, you may not get what you think is the Last record.  The DLast() function will not reliably return the record you think you want.

One possibility is to create a query that orders the records descending by the field(s) you think will get you the "Last" record on top and select the Top 2 records.  Then sort that query ascending and select the Top 1 record.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.