|Stuck with a Dlookup problem? Don't waste your time. Give us a call and we will solve the problem or there is no charge! $100 1/2 hour.
Suppose we have a query that might include records from employee time table and maybe some other tables and the query doesn't need to join to the L_emps table in order to get the main results that you want. But in the form or report you want to show the employee's name somewhere. Here's our first example show how to get the employee's last name:
Create an unbound text field on the form or report. In the Control Source for this unbound field enter the following
=dlookup ("Last_Name","L_Emps","Emp_ID=" & Employee_ID)
The Dlookup has 4 parameters: Dlookup(1,2,3,4):
1 - The name of the field containing the data you want to retrieve
2 - The name of the table or query that contains the data
3 - The field (unique index or primary key) that will be used to retrieve the distinct value for parameter 1. Note the equals sign at the end of this field.
4 - The value of the index or primary key field in 3 above
The result will be the person's last name that matches the current Employee_ID field.
Multiple Dlookup Example for One Field
I bet you're saying.. that's great but I need the first and last names for the person! Here's how that is done - with multiple dlookups in one field Control Source. Here we will show Last Name, First Name
=dlookup("Last_Name","L_Emps","Employee_ID=" & employee_ID) & ", " & dlookup("First_Name","L_Emps","Employee_ID=" & employee_ID)
The above code all goes on one line in the Control Source.
Compound Lookup Function Example for One Report or Form Field
Here's another twist.. suppose the following table setup:
Here we have a foreign key (Department_ID) which points to the department lookup list for which this employee works. In order to display the department on the form or report we basically need to do two dlookup functions in one. Here's the Access Dlookup function example query code:
= Dlookup ("Department","L_Departments","Department_ID=" & Dlookup ("Department_ID","L_Emps","Employee_ID=" & employee_ID))
Note again that both of the above lines should be typed into one line in the Control Source for the unbound field.
More Dlookup Examples:
Copyright 2000-2017 Blue Claw Database Design