Calculating prior Query fields
Inside my query, is four listed fields; date, printer, and a Start and End meter for daily meter readings to keep track of printer impressions. I'm trying to receive a return value by subtracting a meter reading from the START field in a current row and the END meter in the previous row depending on the printer selected in the PRINTER field. This will allow me to view the impressions from the shift before. I believe I need to use a DLOOKUP formula with a WHERE criteria but I'm not sure how to create it. Any help would be appreciated.
Software/Hardware used:
Software/Hardware used:
ASKED:
May 12, 2009 4:24 AM
UPDATED:
May 13, 2009 1:45 PM
Answer Wiki:
You may need to create a function of your own instead of using the DLookup. In the function, you could make a SQL query to find what you need. Depending on how your PK is setup, you might be able to do something like this:
Function GetEndMeter(CurRowID as long) 'Pass in the current record's rowid
dim rs as recordset, SQL as string
SQL = "select EndMeter From table Where rowid = (select max(rowid) from table where rowid < " & CurRowID & ")"
set rs = currentdb().Openrecordset(SQL,dbOpenSnapshot)
if not rs.eof then
GetEndMeter = rs![EndMeter]
end if
rs.close
End Function
How do I implement this answer into my MS 2003 Access database? I created my work inside a query, so should I place it inside a form and use this formula in VBA? Please advise.
To see all answers submitted to the Answer Wiki: View Answer History.
I would create a new VBA module and put the function in there. Then in your query, use this function where you considered using the DLookup function.