Dlookup within a Dlookup

D

Daniel

Good morning,

I am trying to retrieve a specific date using as a criteria a Dlookup function but it won't work and I understand why but I was hoping someone could tell me how I can achieve what I need.

Currently my control source is:
Dlookup("[RfR]","[Drawing Status Tbl]", "[Id]=" & Max(Dlookup("[Id]","[Drawing Status Tbl", "[Drawing Id]=" & [Drawing Id])))

I need to use the latest entry's ID as the criteria that why I used the max function but it seems to return the first entry it encounter. How can I ensure that the ID returned by the second Dlookup is in fact the Greatest ID for the specified Drawing Id?

Thank you

Daniel
 
A

Allen Browne

For the inner one, try DMax() instead of Max(Dlookup())

Alternativley, there is an Extended DLookup() that allows you to specify a
sort order in this link:
http://allenbrowne.com/ser-42.html
Use it something like this:
ELookup("[RfR]", "[Drawing Status Tbl]", "[Drawing Id] = " & [Drawing Id],
"[Id] DESC")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Daniel said:
I am trying to retrieve a specific date using as a criteria a Dlookup
Click to expand...
function but it won't work and I understand why but I was hoping someone
could tell me how I can achieve what I need.
Currently my control source is:
Dlookup("[RfR]","[Drawing Status Tbl]", "[Id]=" &
Click to expand...
Max(Dlookup("[Id]","[Drawing Status Tbl", "[Drawing Id]=" & [Drawing Id])))
I need to use the latest entry's ID as the criteria that why I used the
Click to expand...
max function but it seems to return the first entry it encounter. How can I
ensure that the ID returned by the second Dlookup is in fact the Greatest ID
for the specified Drawing Id?
 
Top