ACC2: DLookup() Usage, Examples, and Troubleshooting Tips

ID: Q136460

The information in this article applies to:

Microsoft Access 2.0

SUMMARY

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

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.

MORE INFORMATION

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


   "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


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

computes to


   "[Employee ID] = " & 7

which, when concatenated, computes to:


   "[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:


   =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:


   =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:


   =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


   =DLookup("[Contact Name]", "Customers", _

    "[Customer ID]='" & [Customer ID]& "'")

-or-


   =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


   "[Customer ID]= '" & [Customer ID]& "'"

computes to


   "[Customer ID]= '" & "ALFKI" & "'"

which, when concatenated, computes to:


   "[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:


   =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:


   =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


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

computes to


   "[Customer ID]= '" & "SIMOB" & "' And [Employee ID] = " & 2

which, when concatenated, computes to:


   "[Customer ID]= 'SIMOB' And [Employee ID] = 2"

You can use the next example to find an employee whose birthday falls on today's date:


   =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:


   "Month([Birth Date]) = " & Month(Date) & " And Day([Birth Date]) = " _
   & Day(Date)

computes to


   "Month([Birth Date]) = " & 12 & " And Day([Birth Date]) = " & 2

which, when concatenated, computes to:


   "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:


   =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:

Open or create a module. From the View menu, choose Immediate window.

Try the function without any criteria. Type the following line in the Immediate window, and then press ENTER:


? DLookUp("[Order ID]", "Orders")

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]

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")

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])

REFERENCES

Microsoft Access "User's Guide," Chapter 18, "Using Expressions in Forms," page 459

Microsoft Access "Language Reference," pages 194-195

Microsoft Access "Building Applications," Chapter 9, "Debugging," pages 213- 216

For more information on how to use the DLookUp() function in Microsoft Access versions 7.0 and 97, please see the following article in the Microsoft Knowledge Base:

Q136122 ACC: DLookup() Usage, Examples, and Troubleshooting (95/97)

Additional query words:

Keywords : kbusage
Version : WINDOWS:2.0
Platform : WINDOWS
Issue type : kbinfo