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

ID: Q108098

The information in this article applies to:

Microsoft Access versions 1.0, 1.1

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 on usage.

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 occur 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 can be useful to 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 an Access Basic 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: the expression, the domain, and the criteria. (Note that the criteria argument is optional.)

You can 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 record set 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, DLookup() returns null.

DLookup() Function Examples

The following examples demonstrate how to use DLookup() 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 DLookup() in its simplest form: without any criteria specified. This example, which refers to the Orders form, returns the value contained in the Last Name field of the first record in the Employees table:


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

NOTE: Field names in expressions need to be enclosed in brackets ([]). The table or query name in the domain does not need to be enclosed in brackets.

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:


   =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 thus 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, first, the individual pieces of the criteria are 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 pointer 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 text, enclose the text in single quotation marks, as in the following example:


   =DLookUp("[Last Name]", "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("[Last Name]", "Employees", "[Last Name] = ""Callahan""")

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

The following example demonstrates how to find the description for a category on the Products form. The Category ID field is a textual key field for the criteria, so the DLookup() statement is


   =DLookUp("[Description]","Categories", _

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

or, equivalently:

   =DLookUp("[Description]","Categories", _

         "[Category ID] = '" & Forms!Products![Category ID] & "'")

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

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

If the current value selected in the Category combo box on the Products form is Beverages, the bound column for the combo box returns BEVR as the Category ID. The original criteria expression


   "[Category ID] = '" & [Category ID] & "'"

computes to

   "[Category ID] = '" & "BEVR" & "'"

which, when concatenated, computes to:

   "[Category ID] = 'BEVR'"

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] = #11-15-67#")

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 Product ID for one of the products supplied by supplier "Formaggi Fortini", with a Supplier ID of 14 (numeric), and a Category ID of DAIR (textual), use the following sample DLookup() statement:


   =DLookUp("[Product ID]", "Products", _

         "[Category ID] = 'DAIR' And [Supplier ID] = 14")

This statement returns Product ID 31, which is the first Product ID that matches the criteria. Product ID 23 also matches the criteria.

The example above uses hard-coded, or specific, Category ID and Supplier ID values. To use variables instead of specifics for the criteria, you could use Access Basic to concatenate multiple string expressions. The following Access Basic example demonstrates this method:


   ' Declare the variables
   Dim CatID As String
   Dim SuppID As Number
   Dim Result

   ' Assign values to the variables to be used in the criteria.
   CatID = "DAIR"
   SuppID = 14

   Result = DLookup("[Product ID]", "Products", _

      "[Category ID] = '" & CatID & "' And [Supplier ID] = " & SuppID)

If the DLookup() function is unsuccessful, the variable Result will contain 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


   "[Category ID] = '" & CatID & "' And [Supplier ID] = " & SuppID

computes to

   "[Category ID] = '" & "DAIR" & "' And [Supplier ID] = " & 14

which, when concatenated, computes to:

   "[Category ID] = 'DAIR' And [Supplier ID] = 14"

The next example can be used 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. The Immediate window can be used 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("[Product ID]", "Products", _

      "[Category ID] = '" & Forms!MyForm![Category ID] _
      & "' And [Supplier ID] = " & Forms!MyForm![Supplier ID])

Note that the criteria includes multiple fields in the lookup criteria, one of which is numeric and one if 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 in the Immediate window, and then press ENTER:

? DLookUp("[Product ID]", "Products")

Microsoft Access performs the calculation and displays the result on the next line in the Immediate window.

Make sure the form references reference data correctly. Type each of the following in the Immediate window, and then press ENTER:

? Forms!MyForm![Category ID]

-and-

? Forms!MyForm![Supplier ID]

Try to get the criteria fields to work independently by hard coding values into the expression. Type each of the following in the Immediate window, and then press ENTER:

? DLookUp("[Product ID]", "Products", "[Category ID] = 'DAIR'")

-and-

? DLookUp("[Product ID]", "Products", "[Supplier ID] = 14)

Try to get the criteria fields to work independently with a parameter in the query. Type each of the following in the Immediate window, and then press ENTER:

? DLookUp("[Product ID]", "Products", _ "[Category ID] = '" & Forms!MyForm![Category ID] & "'")

-and-

? DLookUp("[Product ID]", "Products", _ "[Supplier ID] = " & Forms!MyForm![Supplier ID])

REFERENCES

Microsoft Access "User's Guide," version 1.1, page 651

Microsoft Access "Language Reference," version 1.1, pages 144-146

Microsoft Access "Introduction to Programming," version 1.1, pages 37-42


Keywords          : kbusage ExrOthr 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: March 27, 1999