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.

Bill Prew

Will the prior days reading always be exactly one day before the current reading? Or could there be a larger gap, so we need to look for the latest one before current, as opposed to one day before current?

»bp

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aaron Greene

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.

Bill Prew

So do you need more help?

»bp

Jim Dettman (EE MVE)

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.

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

Open in new window