ACC2000: DLookup() Usage, Examples, and Troubleshooting

ID: Q208786

The information in this article applies to:

Microsoft Access 2000

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

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

SUMMARY

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.

MORE INFORMATION

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 record set that identifies the domain. It can be a table or a query name.

The criteria argument is an optional string expression that you can use 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 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 type the examples in the ControlSource property of a text box on a form or 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.

A Function 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 being evaluated, first the individual pieces of the criteria are evaluated and appended or concatenated; 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 form reference (as in the second example above), Microsoft Access assumes that 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 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 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] & "'")

-or-


   =DLookup("[ContactName]", "[Customers]", _
      "[CustomerID]='" & Forms![Orders]![CustomerID] & "'")

In the second example, the criteria is made up by concatenating three string expression pieces. 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; then the whole value is 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 given date, use the following sample criteria:


   =DLookUp("[LastName]", "Employees", "[BirthDate] = #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 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 (textual), 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:


   ' 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 Result

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


   "[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 12/2/93, 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 then test the components individually in 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 is a tool that you can use to help debug Visual Basic modules. 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 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 textual.

To troubleshoot this expression, try the following:

Press CTRL+G to open the Immediate window.

Try the function without any criteria. To do so, type the following in the Immediate window, and then press ENTER:


? DLookUp("[OrderID]", "Orders")

Make sure the form references are correct. To do so, open the Orders form in the sample database Northwind.mdb and type each of the following lines in the Immediate window, and then press ENTER:


? Forms![MyForm]![CustomerID]

-and-


? Forms![MyForm]![EmployeeID]

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 then press ENTER:


? DLookUp("[OrderID]", "Orders", "[CustomerID] = 'SIMOB'")

-and-


? DLookUp("[OrderID]", "Orders", "[EmployeeID] = 2)

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 then press ENTER:


? DLookUp("[OrderID]", "Orders", _
   "[CustomerID] = '" & Forms![MyForm]![CustomerID] & "'")

-and-


? DLookUp("[OrderID]", "Orders", _
   "[EmployeeID] = " & Forms![MyForm]![EmployeeID])

REFERENCES

For more information about the DCount() and other domain aggregate functions, click Microsoft Access Help on the Help menu, type "domain functions" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words:

Keywords : kbdta ExprFnc
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto