Hi,
I have am making a database for my son in law's building company. I have an employees table where each employee has a day rate for each employee.
I have a costs table which was originally designed to log billable costs, which are input form a form using some combo boxes to select the cost type and building site and the "amount" field for each cost. This feeds an enquiry and report whereby a combo box selects the site to run the report which works fine. I wanted to add a cost category of labour which I have done, and have created a form to input the labour into the costs table. I have created a combo box which used the employees table to give the drop down choice to select the employee and this data goes into the "company" field in the costs table and works fine. I have also a combo box for the site which they worked on for that day. What I want to do now is have access look up in the employees table what their day rate is, and enter it in the amount field when the employee is selected from the combo box.
I have tried to do this using Dlookup with the following string as an after update on the combo box to select the employee.
Amount = DLookup("Day Rate", "Employees", "Employee Name='" & Company & "'")
I want the "amount" to retrieved from the employees table from the Day Rate field when the Employee Name is the same as the Company field (which is where the employee name goes once selected from the combo box on the form), both the Amount field and the Day Rate field are formatted as currency.
When I run this though I get an error state which says Run time error 3075 Syntax error (missing operator) in query expression 'Day Rate'
Any ideas what I am doing wrong?