Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links
Dlookup Function Access Dynamic Lookup



Domain Aggregate Function Home

 

Microsoft Office VBA :
  MS Access 2003
  Access 2007
  Access 2010
  Access 2013


Dlookup Function

Access DLookup: Get the value of a field from a particular record

Dlookup is one of the best tools for creating user friendly Access forms.  Make use 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.

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 function example:

Employee_ID
First_Name
Last_Name

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:

Employee_ID
First_Name
Last_Name
Department_ID

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:











A Blue Claw Database Design Template:

Real Estate Marketing Campaign System
 








A Blue Claw Database Design Article:

How To Create Faster Access Databases
 



Contact Information

Copyright 2000-2014 Blue Claw Database Design