Aaron Greene
asked on
Display value from previous record in current record
I have a table of meter readings that I use to calculate usages. I would like to create a query/database view that would allow me to display the previous day's reading in the same row with the current reading.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The rows will always be spaced at a known interval. Most of the time they will be 24 hour intervals, but there are some instances where they will be spaced 1 hour apart. They will always be consistent.
So do you need more help?
»bp
»bp
Here's a function that you may find easier/faster to use if your doing this in a form.
By passing a form reference (Me or [Form]) and the name of a field, it will return the value from the previous row as currently displayed in the form.
Jim.
By passing a form reference (Me or [Form]) and the name of a field, it will return the value from the previous row as currently displayed in the form.
Jim.
Function GetPreviousRow(frm As Form, strFieldName As String) As Variant
' Fetches value of field from previous row
Dim strBM As String
Dim rstClone As Recordset
' Get the bookmark for the current row.
10 strBM = frm.Bookmark
' Now create the record set clone, and make it
' refer to the same row as rst, which is on the same
' row as the form.
20 Set rstClone = frm.RecordsetClone
30 rstClone.Bookmark = strBM
' Move the clone record set to the previous row.
' If this puts us at the BOF, then the result has to be
' FALSE, and leave the function.
40 rstClone.MovePrevious
50 If rstClone.BOF Then
60 GetPreviousRow = Null
70 Else
' If you're not at BOF, then retrieve the necessary info.
80 GetPreviousRow = rstClone(strFieldName)
90 End If
100 rstClone.Close
110 Set rstClone = Nothing
End Function
»bp