Calculating prior Query fields

20 pts.
Tags:
DLookup
Query optimization
WHERE statement
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Randym
    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.
    1,740 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following