Using Dlookup in queries

[marcoose]

Hi,

I am running a query which includes an EmployeeID field contained in table "Customers".

I would like to do a lookup to another table (Staff)which is not linked to "Customers" which contains the fields StaffID and StaffName in order to drop the Staff_Name into this query.

This involves identifying EmployeeId in the query, finding a match in the Staff table and then dropping the Staff_Name into its own column.

I have tried using dlookup however when I write the following expression :

Dlookup ("[Staff_Name]", "[Staff]", "[EmployeeID]=" & [Staff].[StaffID]

I get an error saying that the expression is typed incorrectly or may be too complex and an Enter Parameter prompt.

Im sure there is an easier way of doing this. Can anyone help ?

 

[kathryn]

two things: I'm not clear on why you can't link the two tables for purposes of your query, using EmployeeID/StaffID?

Your dlookup needs to refer to a field in the query in the criteria.

What is the name of the field in the staff table? is it staffID or employeeID?

I think that your dlookup may have to read
Dlookup ("[Staff_Name]", "[Staff]", "[StaffID]=" & [EmployeeID])



Kathryn

 

[marcoose]

You are right - I actually did not think I could put a join in for the purposes of the query as there was a link to another table which I thought would have confused it.

Thanks a lot - the simplest of fixes are often the most rewarding