Good afternoon.
From a form (frmMeetings) I am trying to build a Dlookup to retrieve the ProgramName field from tblPrograms. However I have to go through another table tblProjects to get the ProgramID by which I can use to get the ProgramName from tblPrograms.
From the form I can Dlookup to tblProjects using ProjectID to obtain the ProgramID. The ProgramID could then be used to lookup ProgramName from tblPrograms.
So far I have =DLookUp("[ProgramID]","[tblProjects]","[ProjectID]='" & [ProjectID] & "'") to obtain the ProgramID but I am not sure if/how to get program name from another table. Can this be done with one lookup statement?
Thank you for any help.
-Scott
Dlookup across two tables
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dlookup across two tables
I think you'll have to nest two DLookup functions:
=DLookup("[ProgramName]","[tblPrograms]","ProgramID='" & DLookUp("[ProgramID]","[tblProjects]","[ProjectID]='" & [ProjectID] & "'") & "'")
This assumes that ProgramID is a text field, just like ProjectID apparently is.
=DLookup("[ProgramName]","[tblPrograms]","ProgramID='" & DLookUp("[ProgramID]","[tblProjects]","[ProjectID]='" & [ProjectID] & "'") & "'")
This assumes that ProgramID is a text field, just like ProjectID apparently is.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Dlookup across two tables
Hans,
Thank you for the quick reply. Both ProgramID and ProjectID are numeric fields. I realize the mistake with the quotes. Sorry.
Thank you for the quick reply. Both ProgramID and ProjectID are numeric fields. I realize the mistake with the quotes. Sorry.
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dlookup across two tables
For others reading this thread: since ProgramID and ProjectID are number fields, the expression should be
=DLookup("[ProgramName]","[tblPrograms]","[ProgramID]=" & DLookUp("[ProgramID]","[tblProjects]","[ProjectID]=" & [ProjectID]))
=DLookup("[ProgramName]","[tblPrograms]","[ProgramID]=" & DLookUp("[ProgramID]","[tblProjects]","[ProjectID]=" & [ProjectID]))
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Dlookup across two tables
Worked perfectly. Thank you very much for your help.