looking up values in a different query

[grgimpy]

My form (SM Developer-ShiftlyPM) has a record source (SM Developer-Shiftly PM Log Query) which is a query. I want to look up a value from a different query (SM Developer-pH Chart Query) to calculate the value for SMD-pHMovingRange below. How can I change the DoCmd code to go to the last record in a different query?

Dim a, b As Variant
DoCmd.GoToRecord , , acLast
a = DLookup("[SMD-pH Lab]", "SM Developer-pH Chart Query")
DoCmd.GoToRecord , , acPrevious
b = DLookup("[SMD-pH Lab]", "SM Developer-pH Chart Query")
Me.SMD_pHMovingRange = Abs(b - a)

 

[grgimpy]

i'm asking the wrong question here. i just realized the DLookup command looks up the same value regardless of what I put in front of it. There is a timestamp on each of my records. How would I write a DMax specifically to look up the entry with the most recent time? Then, I need to also look up the entry with the second most recent time, which I also need help with.

Thanks.

 

[PHV]

Something like this ?
t = DMax("[TimeStamp field]", "SM Developer-pH Chart Query")
a = DLookup("[SMD-pH Lab]", "SM Developer-pH Chart Query", "[TimeStamp field]=#" & t & "#")
t = DMax("[TimeStamp field]", "SM Developer-pH Chart Query", "[TimeStamp field]<#" & t & "#")
b = DLookup("[SMD-pH Lab]", "SM Developer-pH Chart Query", "[TimeStamp field]=#" & t & "#")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886