Dlookup in expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an unbound object which looks up a date:

=DMax("[activitydate]","[tblportfolioactivity]","[portfolioid] = " &
[portfolioid] & " and [valuation] <> 0")

that part works. But then I want to look up the corresponding [valuation]
from that same record. I am trying:

=DLookUp("[valuation]","[tblportfolio]",(DMax("[activitydate]","[tblportfolioactivity]","[portfolioid] = " & [portfolioid] & " and [valuation] <> 0")))

but it doesn't work.

Thanks.

Al
 
You need to add the field name you want to filter on, in the dlookup.

Try this
=DLookUp("[valuation]","[tblportfolio]","[DateFieldName] = #" &
DMax("[activitydate]","[tblportfolioactivity]","[portfolioid] = " &
[portfolioid] & " and [valuation] <> 0") & "#")
 
Thanks Ofer,

It still didn't work. I think it was just too complicated so instead I had
the code create a new table with the records that I needed for this
complicated report/form.

Thanks again for looking at this problem for me.

Ofer said:
You need to add the field name you want to filter on, in the dlookup.

Try this
=DLookUp("[valuation]","[tblportfolio]","[DateFieldName] = #" &
DMax("[activitydate]","[tblportfolioactivity]","[portfolioid] = " &
[portfolioid] & " and [valuation] <> 0") & "#")



AlienzDDS said:
I have an unbound object which looks up a date:

=DMax("[activitydate]","[tblportfolioactivity]","[portfolioid] = " &
[portfolioid] & " and [valuation] <> 0")

that part works. But then I want to look up the corresponding [valuation]
from that same record. I am trying:

=DLookUp("[valuation]","[tblportfolio]",(DMax("[activitydate]","[tblportfolioactivity]","[portfolioid] = " & [portfolioid] & " and [valuation] <> 0")))

but it doesn't work.

Thanks.

Al
Click to expand...
Click to expand...
 
Back
Top