Description of DLookup() usage, examples, and troubleshooting

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q285866
This article has been archived. It is offered "as is" and will no longer be updated.
Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 2000 version of this article, see 208786.
For a Microsoft Access 97 or 95 version of this article, see 136122.
This article shows you how to use the DLookup() function, and includes examples and tips.

The following topics are addressed in this article:
  • The DLookup() function syntax and usage.
  • The following DLookup() function examples:
    • A function with no criteria
    • Specifying numeric criteria
    • Specifying numeric criteria that comes from a field on a form
    • Specifying textual criteria
    • Specifying textual criteria that comes from a field on a form
    • Specifying date criteria
    • Specifying multiple fields in the criteria
  • Tips for troubleshooting and debugging DLookup() expressions.
Note that many difficulties in using the DLookup() function happen when you do not set up and supply the criteria expression correctly. The criteria expression is common to many other aggregate (totals) functions, such as DSum(), DFirst(), DLast(), DMin(), DMax(), and DCount(). Therefore, the following material is useful in helping you understand the domain aggregate functions, as well as the DLookup() function.

The DLookup() Function Syntax and Usage

You can use the DLookup() function in an expression or in a Visual Basic for Applications function to return a field value in a domain or specified set of records.

The syntax of the DLookup() function is as follows:
   DLookup(Expression, Domain [, Criteria])				
The function has three arguments: expression, domain, and criteria (note that the criteria argument is optional).

You use the Expression argument to identify the field that contains the data in the domain that you want returned or to perform calculations using the data in that field.

The Domain argument is the name of the recordset that identifies the domain. It can be a table or a query.

The Criteria argument is an optional string expression that you can use to restrict the range of the data on which the DLookup() function is performed. Note that the criteria argument is identical to the WHERE clause in a SQL expression (except that you do not use the keyword WHERE).

The DLookup() function returns one value from a single field, even if more than one record satisfies the criteria. If no record satisfies the criteria or if the domain contains no records, DLookup() returns a Null.

DLookup() Function Examples

The following examples demonstrate how you can use DLookup() to find or return values from a table or query. These examples refer to the sample database Northwind.mdb, and you can enter the examples in the ControlSource property of a text box on a form or a report.

NOTE: In the following sample expressions, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating these expressions.

Functions with No Criteria:

This example demonstrates how to use the DLookup() function in its simplest form, without specifying any criteria. This example returns the value contained in the LastName field of the first record in the Employees table:
   =DLookup("[LastName]", "Employees")				

Specifying Numeric Criteria:

To find the last name of the employee with ID number 7, specify a criteria to limit the range of records used:
   =DLookup("[LastName]", "Employees", "[EmployeeID] = 7")				

Specifying Numeric Criteria That Comes from a Field on a Form:

If you do not want to specify a particular value in the expression (as in the previous example), use a parameter in the criteria instead. The following examples demonstrate how to specify criteria from another field on the current form. You can try these on the Orders form in the sample database Northwind.mdb.
   =DLookup("[LastName]", "Employees", _      "[EmployeeID] = Form![EmployeeID]")   =DLookup("[LastName]", "Employees", _      "[EmployeeID] = " & [EmployeeID])   =DLookup("[LastName]", "Employees", _      "[EmployeeID] = " & Forms![Orders]![EmployeeID])				
These three examples return the same results.

In the first example, Form![EmployeeID] appears inside the criteria's quotation marks. "Form" tells Microsoft Access that the field reference, "EmployeeID," comes from the current form. If you omit it, Microsoft Access compares EmployeeID to itself in the Employees table and returns the last name from the first record in the Employees table (the same result as if you did not specify any criteria). This is because the first record in the Employees table has a 1 in the EmployeeID field, so the argument:
   "[EmployeeID] = [EmployeeID]"				
computes to:
   "1 = 1"				
and would therefore be the first record that matched the criteria.

The criteria for the other two examples are made by concatenating two string expressions with an ampersand (&). In the third example, the criteria ends with a form field reference.

When criteria are evaluated, the individual pieces of the criteria are evaluated first and appended or concatenated, and then the whole value is computed.

If the current value in the EmployeeID field on the Orders form is 7, the original criteria expression
   "[EmployeeID] = " & [EmployeeID]				
would compute to
   "[EmployeeID] = " & 7				
which, when concatenated, computes to:
   "[EmployeeID] = 7"				
If you do not specify the full forms reference (as in the second example above), Microsoft Access assumes you are referring to the current form.

The following example is a derivative of the third example above:
   =DLookup("[LastName]", "Employees", _      "[EmployeeID] = Forms![Orders]![EmployeeID]")				
In this example, the full forms reference is enclosed inside the criteria's quotation marks. In this case, Microsoft Access correctly looks up the value when the form first opens, but only until the EmployeeID field is changed by modifications to a record, or the addition of a record. Microsoft Access does not automatically recompute the new employee last name value after such a change. You can manually recompute the expression by placing the insertion point on the control and pressing F9.

If you want the field to update automatically when the criteria changes, make the criteria a variable by using the method of concatenating the expression's parts as described earlier. Note that when you move to a new record, the DLookup() text boxes that update automatically will have #Error in them until you enter something in the EmployeeID text box.

Specifying Textual Criteria:

All the previous examples demonstrate how to use the DLookup() function with numeric criteria. If the criteria fields are text, enclose the text in single quotation marks, as in the following example:
   =DLookup("[Title]", "Employees", "[LastName] = 'Callahan'")				
Note that you can use double quotation marks instead of single quotation marks, although single quotation marks are preferred. Use two double quotation marks to replace one single quotation mark. The following example uses double quotation marks and is equivalent to the example above:
   =DLookup("[Title]", "Employees", "[LastName] = ""Callahan""")				

Specifying Textual Criteria That Comes from a Field on a Form:

The following example demonstrates how to find the contact name for a customer on the Orders form. The CustomerID field is a textual key field for the criteria, so the DLookup() statement is:
   =DLookup("[ContactName]", "[Customers]", _      "[CustomerID]='" & [CustomerID] & "'")				
   =DLookup("[ContactName]", "[Customers]", _      "[CustomerID]='" & Forms![Orders]![CustomerID] & "'")				
In the second example, the criteria is created by concatenating three string expressions. The first is "[CustomerID] = '", the second is the value contained in the actual CustomerID field, and the third is the closing single quotation mark enclosed in double quotation marks.

When this criteria is evaluated, first the individual pieces are evaluated and their results appended or concatenated. The whole value is then computed.

If the current value selected in the CustomerID combo box on the Orders form is Alfreds Futterkiste, the bound column for the combo box returns ALFKI as the CustomerID. The original criteria expression
   "[CustomerID] = '" & [CustomerID] & "'"				
evaluates as
   "[CustomerID] = '" & "ALFKI" & "'"				
which, when concatenated, evaluates as
   "[CustomerID] = 'ALFKI'"				

Specifying Date Criteria:

If the criteria fields are date or time values, enclose the date or time value in number signs (#). To find an employee whose birthday is on a specified date, use the following sample criteria:
   =DLookup("[LastName]", "Employees", "[BirthDate] = #12-08-68#")				

Specifying Multiple Fields in the Criteria:

The criteria expression can be any valid SQL WHERE clause (without the keyword WHERE). This implies that more than one field can be used to specify criteria for a DLookup() function.

To find the OrderID for one of the orders sold by employee "Andrew Fuller" with an EmployeeID of 2 (numeric) for customer "Simons bistro" with a CustomerID of SIMOB (text), use the following sample DLookup() statement:
   =DLookup("[OrderID]", "Orders", _      "[CustomerID] = 'SIMOB' And [EmployeeID] = 2")				
This statement returns OrderID 10556, which is the first OrderID that matches the criteria. OrderID 10669 also matches the criteria.

The example above uses hard-coded, or specific, CustomerID and EmployeeID values. To use variables instead of specific values for the criteria, you can use Visual Basic for Applications to concatenate multiple string expressions. The following Visual Basic example demonstrates this method:
Sub subTest    'Declare the variables.    Dim CustID As String    Dim EmpID As Long    Dim Result    'Assign values to the variables to be used in the criteria.    CustID = "SIMOB"    EmpID = 2    Result = DLookup("[OrderID]", "Orders", _             "[CustomerID] = '" & CustID & "' And [EmployeeID] = " & EmpID)   MsgBox ResultEnd Sub				
If the DLookup() function is unsuccessful, the variable Result contains a Null.

Note that the criteria is composed of four pieces that are evaluated individually. The results are appended and evaluated as a whole. The original criteria expression:
   "[CustomerID] = '" & CustID & "' And [EmployeeID] = " & EmpID				
evaluates as
   "[CustomerID] = '" & "SIMOB" & "' And [EmployeeID] = " & 2				
which, when concatenated, evaluates as
   "[CustomerID] = 'SIMOB' And [EmployeeID] = 2"				
You can use the next example to find an employee whose birthday falls on today's date:
   =DLookup("[LastName]", "Employees", _      "Month([BirthDate]) = " & Month(Date) & " And Day([BirthDate]) _      = " & Day(Date))				
If today's date is 2/12/2001, the original criteria expression
   "Month([BirthDate]) = " & Month(Date) & " And Day([BirthDate]) = " _      & Day(Date)				
evaluates as
   "Month([BirthDate]) = " & 12 & " And Day([BirthDate]) = " & 2				
which, when concatenated, evaluates as
   "Month([BirthDate]) = 12 And Day([BirthDate]) = 2"				

Tips for Troubleshooting and Debugging DLookup() Expressions

To troubleshoot expressions, break down the expression into smaller components and test the components individually within the Immediate Window to ensure that they work correctly. If the smaller components work correctly, you can put them back together, piece by piece, until the final expression works correctly.

The Immediate Window can be used to help debug Visual Basic modules. Use the Immediate Window to test and evaluate expressions independently of the form or macro where the expression is used. You can set up expressions in the Immediate Window, run them, and see the results immediately.

The following example demonstrates a strategy to break down a DLookup() expression into smaller components that you can test in the Immediate Window. Assume that you are having difficulty with the following statement:
   =DLookup("[OrderID]", "Orders", _      "[CustomerID] = '" & Forms![MyForm]![CustomerID] _      & "' And [EmployeeID] = " & Forms![MyForm]![EmployeeID])				
Note that the criteria includes multiple fields in the lookup criteria, one of which is numeric, and one of which is text.

To troubleshoot this expression, try the following:
  1. Press CTRL+G to open the Immediate window.
  2. Test the function with no criteria. To do so, type the following in the Immediate Window, and then press ENTER:
    ? DLookup("[OrderID]", "Orders")					
  3. Verify that the forms references are correct. To do so, open the Orders form in the sample database Northwind.mdb, and enter each of the following lines in the Immediate Window. Press ENTER after each line.
    ? Forms![MyForm]![CustomerID]					
    ? Forms![MyForm]![EmployeeID]					
  4. Try to get the criteria fields to work independently by hard coding values into the expression. To do so, type each of the following lines in the Immediate Window, and press ENTER after each:
    ? DLookup("[OrderID]", "Orders", "[CustomerID] = 'SIMOB'")					
    ? DLookup("[OrderID]", "Orders", "[EmployeeID] = 2)					
  5. Try to get the criteria fields to work independently with a parameter in the query. To do so, type each of the following in the Immediate Window, and press ENTER after each one:
    ? DLookup("[OrderID]", "Orders", _   "[CustomerID] = '" & Forms![MyForm]![CustomerID] & "'")					
    ? DLookup("[OrderID]", "Orders", _   "[EmployeeID] = " & Forms![MyForm]![EmployeeID])					
inf ACC2002 reviewdocid

Article ID: 285866 - Last Review: 12/05/2015 23:59:02 - Revision: 5.3

Microsoft Office Access 2003, Microsoft Access 2002 Standard Edition

  • kbnosurvey kbarchive kbprogramming kbfunctions kbdta kbfaq kbhowto KB285866