Nesting DLOOKUPs

[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!!

 

[Dale Fye]

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]

I'm still having trouble. If EMPNO is a number, how should it look? The
others should be text.
--
GD

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.

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!!

 

[GD]

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

=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.

 

[Dirk Goldgar]

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?

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.