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

enter image description here

Please verify which version of Access you are using. – cowb0y Aug 11 '14 at 22:55 — cowb0y, Aug 11 '14 at 22:55
What is Access 2005? I have not heard of this. – Brad Aug 11 '14 at 22:57 — Brad, Aug 11 '14 at 22:57
My bad, its 2003. Apologies I typed it wrongly. – Explorer Aug 12 '14 at 02:38 — Explorer, Aug 12 '14 at 02:38

score 2 · Accepted Answer · answered Aug 11 '14 at 22:57

2

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.

answered Aug 11 '14 at 22:57

cowb0y

187
7

Thanks cowboy, I changed main sql query of the form. (hard way but it worked.) Thanks – Explorer Aug 13 '14 at 18:50
If a form is read-only (just displaying records), then pulling in additional fields using a JOIN is often simplest. However, be aware that some types of joins will make the resulting dataset read-only, and not all database products will allow you to edit all or part of a joined recordset (Access is very accomodating). Something to keep in mind. – cowb0y Aug 13 '14 at 22:37

Joining two tables in Expression Builder

1 Answers1