DLookup Function

In Access desktop databases, you can use the DLookup function to get the value of a particular field from a specified set of records (a domain). You can use DLookup in a Visual Basic for Applications (VBA) module, a macro, a query expression, or a calculated control on a form or report.

You can use the DLookup function to display the value of a field that isn't in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the ProductName field is in another table, the Products table. You can use the DLookup function in a calculated control to display ProductName on the same form.

Syntax

DLookup(expr, domain [, criteria])

The DLookup function has these arguments:

Argument	Description
expr	Required. An expression that identifies the field whose value you want to return. It can be a string expression that identifies a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain	Required. A string expression that identifies the set of records that makes up the domain. It can be a table name or a query name for a query that doesn't require a parameter.
criteria	Optional. A string expression used to restrict the range of data on which the DLookup function runs. For example, criteria is often equivalent to the WHERE clause in a SQL expression, without the word WHERE. If you omit criteria, the DLookup function evaluates expr against the entire domain. Any field included in criteria must also be a field in domain. Otherwise, the DLookup function returns Null.

Remarks

The DLookup function returns a single field value based on the information specified in criteria. Although criteria is optional, if you don't supply a value for it, the DLookup function returns a random value in the domain.

If no record satisfies criteria, or if domain contains no records, the DLookup function returns Null.

If more than one field meets criteria, the DLookup function returns the first occurrence. You should specify criteria that ensure the field value returned by DLookup is unique. You might want to use a primary key value for your criteria, such as [EmployeeID] in the following example, to ensure that DLookup returns a unique value:

Dim varX As Variant
varX = DLookup("[LastName]", "Employees", _
    "[EmployeeID] = 1")

Whether you use the DLookup function in a macro or module, a query expression, or a calculated control, construct the criteria argument carefully so that Access evaluates it correctly.

You can use the DLookup function to specify criteria in the Criteria row of a query, within a calculated field expression in a query, or in the Update To row in an update query.

You can also use the DLookup function in an expression in a calculated control on a form or report if the field that you need to display isn't in the record source on which your form or report is based. For example, suppose you have an Order Details form based on an Order Details table with a text box called ProductID that displays the ProductID field. To look up ProductName from a Products table based on the value in the text box, create another text box and set its ControlSource property to the following expression:

=DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order Details]!ProductID)

Tips

• Although you can use the DLookup function to display a value from a field in a foreign table, it may be more efficient to create a query that contains the fields that you need from both tables and then to base your form or report on that query.
• You can also use the Lookup Wizard to find values in a foreign table.

Example

The following example returns name information from the CompanyName field of the record that satisfies criteria. The domain is a Shippers table. The criteria argument restricts the resulting set of records to those for which ShipperID equals 1.

Dim varX As Variant
varX = DLookup("[CompanyName]", _
    "Shippers", "[ShipperID] = 1")

The next example from the Shippers table uses the form control ShipperID to provide criteria for the DLookup function. The reference to the control isn't included in the quotation marks that denote the strings. This ensures that each time DLookup runs, Access gets the current value from the control.

Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", _
    "[ShipperID] = " & Forms!Shippers!ShipperID)

The next example uses a variable, intSearch, to get the value.

Dim intSearch As Integer
Dim varX As Variant
intSearch = 1
varX = DLookup("[CompanyName]", "Shippers", _
    "[ShipperID] = " & intSearch)