Access DLookup: Get the value of a field from a particular
Dlookup is one of the best tools for creating user friendly Access forms.
of Lookup Lists where ever possible to provide a consistent and efficient method
for data entry. The downside of this technique is
that some queries can get really complicated with a dozen or more lookup
tables in the query design grid.
We have dozens of downloadable Access small business databases for you to learn and examine.
15 years ago we were putting Access database on the web using Active Server Page (ASP) programming language for the user interface. See examples of Access on the Internet.
If you have a Microsoft Access database setup as a
frontend/backend for multi user environments then, ideally, you should place
static lookup lists in the front end. This will reduce network traffic and
won't hinder other users when you use lots of Dlookups or you use the
Dlookup function against a large table. In the cases where you can't
put the look up lists in the front end because they change frequently then
the domain lookup or dynamic lookup function should not be used.
Instead, lookup the value in the query for the form or report using a
regular join to the lookup table (not a dlookup in the query).
Here's the table (Table L_Emps) setup for our Access Dlookup/Vlookup
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
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=" &
The Dlookup has 4 parameters: Dlookup(1,2,3,4):
1 - The name of the field containing the data you want
2 - The name of the table or query that contains the
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
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
employee_ID) & ", " & dlookup("First_Name","L_Emps","Employee_ID=" &
The above code all goes on one line in the Control
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: