Dlookup and Dmax

[Edgarr8]

Hi,

I am trying to get a text box to go back and grab the last date of info entered and then use that to do a lookup and return a value. here is what I have.

This brings back the last Date that data was entered for 001-01 ( Block)
Expr1: DMax("[MeasurementDate]","Rafa Update Sheet","[R/B]= '001-03'")

and this one brings back the PSI that i need, but it brings back the first one entered.
Expr2: DLookUp("[Psi Final]", "Rafa Update Sheet","[R/B]= '001-03'"

Well I can get exacly what i need by doing this
Expr4: DLookUp("[Psi Final]","Rafa Update Sheet","[R/B]= '001-03'" & " And [MeasurementDate]= #07/20/2009#")


But I would like to use the DMax to bring back the last date,

Can you help me put it together?

This is the farthest I gone...
DLookUp("[Psi Final]", "Rafa Update Sheet","[R/B]= '001-03'" & " And [MeasurementDate]= DMax("[MeasurementDate]","Rafa Update Sheet","[R/B]= '001-03'"))

Thank You,

 

[RossWindows]

Maybe if you calculated DMax("[MeasurementDate]","Rafa Update Sheet","[R/B]= '001-03'")
and stored it in a date variable called dtmMaxMeasurementDate
Then did your next calculation like
DLookUp("[Psi Final]","Rafa Update Sheet","[R/B]= '001-03'" & " And [MeasurementDate]= #" & dtmMaxMeasurementDate & "#)"

This is purely air-code, just for conceptual purposes.
You may have to play with the syntax a little to make it work.

 

[Edgarr8]

DLookUp("[Psi Final]","Rafa Update Sheet","[R/B]= '001-03'" & " And [MeasurementDate]= #" & dtmMaxMeasurementDate & "#")
Moved the ) to the end, then it worked flawlessly and it returned the value I needed, that will work, but if anyone has an idea how to combine them , let me know.

And thank you very much for your response

 

[bulbisi]

Got the same thing: 2 conditions into a dLookup but with a Max in there.
in fact, my Dmax is in another function (as I need it as well)

How to use my Dmax function result? (called Loan2FRbatchNum() ) Basic idea is in green

my dMax side =

Public Function Loan2FRbatchNum()
Dim LoanBatchNum
LoanBatchNum = (DLookup("max([BatchNum])", "[LastPrintDate]", "[query] = 'Loan2FR'"))
Loan2FRbatchNum = LoanBatchNum
End Function

my dlookup side =

Public Function Loan2FRdate()
Dim Loandate
Loandate = (DLookup("[BatchTime]", "[LastPrintDate]", "[query] = 'Loan2FR'"[COLOR=green] AND "[BatchNum] = Loan2FRbatchnum"[/COLOR]))
Loan2FRdate = Loandate
End Function

I get stuck on it, I'm still a beginner finally...

Thanks in advance

Chris