Microsoft KB Archive/136460

= ACC2: DLookup Usage, Examples, and Troubleshooting Tips =

Article ID: 136460

Article Last Modified on 6/24/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q136460



Novice: Requires knowledge of the user interface on single-user computers.



SUMMARY
This article describes 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  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 

When you use the DLookup function, difficulties may result if 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 may help you understand those domain aggregate functions as well as the DLookup function.



The DLookup Function Syntax and Usage
The DLookup function can be used in an expression or in a user-defined function to return a field value in a domain or a specified set of records.

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

The expression argument is used to identify the field that contains the data in the domain that you want returned or is used 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 name.

The criteria argument is an optional string expression that is used to restrict the range of the data that the DLookup function is performed on.

Note that the criteria argument is identical to the WHERE clause in an 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, the DLookup function returns a null.

DLookup Function Examples
The following examples demonstrate how to use the DLookup function to find or return values from a table or query. These examples refer to the sample database NWIND.MDB and can be entered in the ControlSource property of a text box on a form or report.

NOTE: In the following sample code, 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 this code.

A Function with No Criteria
This example demonstrates how to use the DLookup function without any criteria specified. This example returns the value contained in the Last Name field of the first record in the Employees table:   =DLookUp("[Last Name]", "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("[Last Name]", "Employees", "[Employee ID] = 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 example above), 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 examples on the Orders form in the sample database NWIND.MDB:   =DLookUp("[Last Name]", "Employees", _

"[Employee ID] = Form![Employee ID]")

=DLookUp("[Last Name]", "Employees", _

"[Employee ID] = " & [Employee ID])

=DLookUp("[Last Name]", "Employees", _

"[Employee ID] = " & Forms![Orders]![Employee ID]) The three examples above return the same results.

In the first example, Form![Employee ID] appears inside the criteria's quotation marks. "Form" tells Microsoft Access that the field reference "Employee ID" comes from the current form. If you omit it, Microsoft Access compares Employee ID 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 Employee ID field, so the argument   "[Employee ID] = [Employee ID]" computes to <pre class="fixed_text">  "1 = 1" and would therefore be the first record that matched the criteria.

The criteria for the second 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 being evaluated, the individual pieces of the criteria are first evaluated, and then appended or concatenated. Then, the whole value is computed.

If the current value in the Employee ID field on the Orders form is 7, the original criteria expression <pre class="fixed_text">  "[Employee ID] = " & [Employee ID] computes to <pre class="fixed_text">  "[Employee ID] = " & 7 which, when concatenated, computes to: <pre class="fixed_text">  "[Employee ID] = 7" If you do not specify the full form reference (which the second example above does not), Microsoft Access assumes that you are referring to the current form.

The following example is a derivative of the third example above: <pre class="fixed_text">  =DLookUp("[Last Name]", "Employees", _

"[Employee ID] = Forms![Orders]![Employee ID]") In this example, the full form 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 Employee ID 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 then pressing F9.

If you want the field to automatically update 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 Employee ID text box.

Specifying Textual Criteria
All the examples above demonstrate how to use the DLookup function with numeric criteria. If the criteria fields are textual, enclose the text in single quotation marks, as in the following example: <pre class="fixed_text">  =DLookUp("[Title]", "Employees", "[Last Name] = '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: <pre class="fixed_text">  =DLookUp("[Title]", "Employees", "[Last Name] = ""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 Customer ID field is a textual key field for the criteria, so the DLookup statement is <pre class="fixed_text">  =DLookup("[Contact Name]", "Customers", _

"[Customer ID]='" & [Customer ID]& "'") -or- <pre class="fixed_text">  =DLookup("[Contact Name]", "Customers", _

"[Customer ID]='" & Forms![Orders]![Customer ID]& "'") In the second example, the criteria is made up by concatenating three string expression pieces. The first is "[Customer ID]= '", the second is the value contained in the actual Customer ID field, and the third is the closing single quotation mark enclosed in double quotation marks.

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

If the current value selected in the Customer ID combo box on the Orders form is Alfreds Futterkiste, the bound column for the combo box returns ALFKI as the Customer ID. The original criteria expression <pre class="fixed_text">  "[Customer ID]= '" & [Customer ID]& "'" computes to <pre class="fixed_text">  "[Customer ID]= '" & "ALFKI" & "'" which, when concatenated, computes to: <pre class="fixed_text">  "[Customer ID]= '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 given date, use the following sample criteria: <pre class="fixed_text">  =DLookUp("[Last Name]", "Employees", "[Birth Date] = #01-27-66#")

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 Order ID for one of the orders sold by employee "Andrew Fuller", with an Employee ID of 2 (numeric), for customer "Simons bistro", with a Customer ID of SIMOB (textual), use the following sample DLookup statement: <pre class="fixed_text">  =DLookUp("[Order ID]", "Orders", _

"[Customer ID]= 'SIMOB' And [Employee ID] = 2") This statement returns Order ID 10556, which is the first Order ID that matches the criteria. Order ID 10669 also matches the criteria.

The example above uses hard-coded (or specific) Customer ID and Employee ID values. To use variables instead of specifics for the criteria, you can use Access Basic to concatenate multiple string expressions. The following Access Basic example demonstrates this method: ' 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("[Order ID]", "Orders", _

"[Customer ID]= '" & CustID & "' And [Employee ID] = " & EmpID)

MsgBox Result If the DLookup function is unsuccessful, the variable Result contains Null.

Note that the criteria is made up of four pieces that are evaluated individually. The results are appended, and then evaluated as a whole. The original criteria expression <pre class="fixed_text">  "[Customer ID]= '" & CustID & "' And [Employee ID] = " & EmpID computes to <pre class="fixed_text">  "[Customer ID]= '" & "SIMOB" & "' And [Employee ID] = " & 2 which, when concatenated, computes to: <pre class="fixed_text">  "[Customer ID]= 'SIMOB' And [Employee ID] = 2" You can use the next example to find an employee whose birthday falls on today's date: <pre class="fixed_text">  =DLookUp("[Last Name]", "Employees", _

"Month([Birth Date]) = " & Month(Date) & " And Day([Birth Date]) _     = " & Day(Date)) If today's date is 12/2/93, the original criteria expression: <pre class="fixed_text">   "Month([Birth Date]) = " & Month(Date) & " And Day([Birth Date]) = " _   & Day(Date) computes to <pre class="fixed_text">   "Month([Birth Date]) = " & 12 & " And Day([Birth Date]) = " & 2 which, when concatenated, computes to: <pre class="fixed_text">   "Month([Birth Date]) = 12 And Day([Birth Date]) = 2"

Tips for Troubleshooting and Debugging DLookup Expressions
To troubleshoot expressions, break down the expression into smaller components and test the components individually in the Immediate window to ensure they work correctly. If the smaller components work correctly, they can be put back together, piece by piece, until the final expression works correctly.

The Immediate window is a tool that you can use to help debug Access Basic modules. You can use the Immediate window to test and evaluate expressions independently of the form or macro the expression is to be used in. You can set up expressions in the Immediate window, run them, and see the results immediately.

The following example demonstrates a strategy you can use to break down a DLookup expression into smaller components that you can test in the Immediate window. Assume you are having difficulty with the following statement: <pre class="fixed_text">  =DLookUp("[Order ID]", "Orders", _

"[Customer ID]= '" & Forms![MyForm]![Customer ID] _ & "' And [Employee ID] = " & Forms![MyForm]![Employee ID]) Note that the criteria includes multiple fields in the lookup criteria, one of which is numeric and one of which is textual.

To troubleshoot this expression, try the following:

<ol> Open or create a module. From the View menu, choose Immediate window.</li>  Try the function without any criteria. Type the following line in the Immediate window, and then press ENTER: ? DLookUp("[Order ID]", "Orders") Microsoft Access will perform the calculation and display the result on the next line in the Immediate window. </li>  Make sure the form references data correctly data. Type each of the following lines in the Immediate window, and then press ENTER: ? Forms![MyForm]![Customer ID] -and- ? Forms![MyForm]![Employee ID] </li>  Try to make the criteria fields work independently by hard coding values into the expression. Type each of the following lines in the Immediate window, and then press ENTER: ? DLookUp("[Order ID]", "Orders", "[Customer ID]= 'SIMOB'") -and- ? DLookUp("[Order ID]", "Orders", "[Employee ID] = 2") </li>  Try to make the criteria fields work independently with a parameter in the query. Type each of the following lines in the Immediate window, and then press ENTER: ? DLookUp("[Order ID]", "Orders", _ "[Customer ID]= '" & Forms![MyForm]![Customer ID]& "'") -and- ? DLookUp("[Order ID]", "Orders", _ "[Employee ID] = " & Forms![MyForm]![Employee ID]) </li></ol>

<div class="references_section">