Joining two tables in Expression Builder

Question

I am a new user to ms access and just now started using MS access 2003. I want to apply equality join in expression builder in a form. I want to populate Employee.EmployeeLastName from Employee table when Employee.EmpNo = SalesHistoryToExcelFormRawData.ordSalesRep

I am trying this:

=[Employees]![EmpLastName] WHERE( ([SalesHistoryToExcelFormRawData]![ordSalesRep]) = ([Employees]![EmpNo]) )

But I am getting invalid syntax error. Help will be appreciated. I spent a lot of time in googling how to use where clause in expression builder but no luck :( Is there any other way I can achieve this.

I am novice to ms access and started using two days ago. Please let me know if my approach is wrong.

Thanks

Answer 1

You should probably be using Dlookup() in your expression. Example: =Dlookup("[EmpLastName]","[Employees]","[EmpNo]=" & [ordSalesRep]))". Read the help for Dlookup, as it is very useful in a form context.