Dlookup across two tables

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Dlookup across two tables

Post by scottb »

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

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dlookup across two tables

Post by HansV »

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.
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Dlookup across two tables

Post by scottb »

Hans,
Thank you for the quick reply. Both ProgramID and ProjectID are numeric fields. I realize the mistake with the quotes. Sorry.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dlookup across two tables

Post by HansV »

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]))
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Dlookup across two tables

Post by scottb »

Worked perfectly. Thank you very much for your help.