Dlookup within a Dlookup

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

Guest

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
 
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?
 
Back
Top