Nesting DLOOKUPs

  • Thread starter Thread starter GD
  • Start date Start date
G

GD

Can I nest a DLOOKUP within another DLOOKUP expression? I've tried:

=DLookUp("[tblEMPLOYEE]![NAME]","tblEMPLOYEE","[tblEMPLOYEE]![EMPLOYEE]='" &
DLookUp("[tblDMLOG]![EMPNO]","tblDMLOG","[tblDMLOG]![DM#]='" &
Forms!frmGregTest!Text40 & "'"))

The 2nd DLOOKUP is getting the employee # from tblDMLOG, and I want to use
that to get the employee name from tblEMPLOYEE. The 2nd one works by itself.
What is wrong with my expression?

Thanks for your invaluable time!!
 
I think you are missing an extra set of closing quotes. Try:

=DLookUp("[NAME]", "tblEMPLOYEE", "[EMPLOYEE]='" & DLookUp("[EMPNO]",
"tblDMLOG", "[DM#]='" & Forms!frmGregTest!Text40 & "'") & "'")


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
I'm still having trouble. If EMPNO is a number, how should it look? The
others should be text.
--
GD


Dale Fye said:
I think you are missing an extra set of closing quotes. Try:

=DLookUp("[NAME]", "tblEMPLOYEE", "[EMPLOYEE]='" & DLookUp("[EMPNO]",
"tblDMLOG", "[DM#]='" & Forms!frmGregTest!Text40 & "'") & "'")


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



GD said:
Can I nest a DLOOKUP within another DLOOKUP expression? I've tried:

=DLookUp("[tblEMPLOYEE]![NAME]","tblEMPLOYEE","[tblEMPLOYEE]![EMPLOYEE]='" &
DLookUp("[tblDMLOG]![EMPNO]","tblDMLOG","[tblDMLOG]![DM#]='" &
Forms!frmGregTest!Text40 & "'"))

The 2nd DLOOKUP is getting the employee # from tblDMLOG, and I want to use
that to get the employee name from tblEMPLOYEE. The 2nd one works by itself.
What is wrong with my expression?

Thanks for your invaluable time!!
Click to expand...
Click to expand...
 
We're almost there, Chris =)

The following gets me what I need when there's an entry in Text40:

=DLookUp("[NAME]","tblEMPLOYEE","[EMP#]=" &
DLookUp("[EMPNO]","tblDMLOG","[DM#]='" & Forms!frmGregTest!Text40 & "'"))

My problem now is that #Error appears when Text40 is blank. Can I fix that?

--
GD


Chris O'C via AccessMonster.com said:
=DLookup("[NAME]", "tblEMPLOYEE", "EMPLOYEE='" _
& DLookup("EMPNO", "tblDMLOG", "[DM#]=" & Forms!frmGregTest!Text40) _
& "'")

Chris

I'm still having trouble. If EMPNO is a number, how should it look? The
others should be text.
Click to expand...
Click to expand...
 
GD said:
We're almost there, Chris =)

The following gets me what I need when there's an entry in Text40:

=DLookUp("[NAME]","tblEMPLOYEE","[EMP#]=" &
DLookUp("[EMPNO]","tblDMLOG","[DM#]='" & Forms!frmGregTest!Text40 & "'"))

My problem now is that #Error appears when Text40 is blank. Can I fix
that?
Click to expand...


I suspect that this would work for you:

=DLookUp("[NAME]","tblEMPLOYEE","[EMP#]=" &
DLookUp("[EMPNO]","tblDMLOG","[DM#]=[Forms]![frmGregTest]![Text40]"))

That expression was wrapped to two lines for posting, but it should all be
on one line in practice.

I believe the DLookup function will evaluate form/control references
embedded in the criteria. If I'm wrong, let me know.
 
Back
Top