You can use the DLookup function to get the value of a particular field from a specified set of records (domain). Use DLookup in a macro or module, in a query expression, or in a calculated control on a form or report.
You can use DLookup to display the value of a field that is not 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 could use DLookup in a calculated control to display the ProductName on the same form.
DLookup(expr, domain[, criteria])
The DLookup function uses the following arguments.
| Part | Description |
| expr | Expression that identifies the field whose value you want to return. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. You can include in expr the name of a table field, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function. |
| domain | String expression identifying the set of records that constitutes the domain. It can be a table name or a query name. |
| criteria | Optional string expression used to restrict the range of data on which DLookup is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, DLookup evaluates expr against the entire domain. Any field included in criteria must also be a field in domain; otherwise, DLookup returns a Null. |
DLookup finds a value in the field or expression defined by expr from the table or query referred to by domain, according to any restrictions specified by criteria.
DLookup returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don’t supply a value for criteria, DLookup returns a random value in the domain.
If no record satisfies criteria or if domain contains no records, DLookup returns a Null.
If more than one field meets criteria, DLookup returns the first occurrence. You should specify criteria that will ensure that the field value returned by DLookup is unique. You may 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.
X = DLookup("[LastName]", "Employees", "[EmployeeID] = 1")
Whether you use DLookup in a macro or module, a query expression, or a calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.
You can use DLookup 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 DLookup in an expression in a calculated control on a form or report if the field that you need to display is not in the record source on which your form or report is based. For example, suppose you have an Orders form with a text box called SKU that displays the ProductID field, and you want to look up ProductName from a Products table, based on the value in the text box. You could create another text box and set its ControlSource property to the following expression.
=DLookup("[ProductName]", "Products", "[ProductID] ='" _
& Forms!Orders!SKU & "'")
Tips Although you can use DLookup 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 base your form or report on that query.
You can also use the Lookup Wizard to find values in a foreign table.
Note Unsaved changes to records in domain are not included when you use this function. If you want the DLookup function to be based on the changed values, you must first save the changes by choosing the Save Record command from the File menu, moving the focus to another record, or using the Update method.
Domain Aggregate Functions.
This example returns name information from the CompanyName field of the record satisfying criteria. The domain is a Shippers table. The criteria argument restricts the resulting set of records to those for which ShipperID equals 1.
Dim strX As String= DLookup("[CompanyName]", "Shippers", "[ShipperID] = 1")
The following example from the Shippers table uses the form control ShipperID to provide criteria for DLookup. Note that the reference to the control is not included in the quotation marks that denote the strings. This ensures that each time the DLookup function is called, Microsoft Access will obtain the current value from the control.
Dim strX As String= DLookup("[CompanyName]", "Shippers", "[ShipperID] =" _
& Forms!Shippers!ShipperID)
The next example uses a variable, intSearch, to perform a lookup.
Dim intSearch As Integer, strX As String= 1= DLookup("[CompanyName]", "Shippers", _
"[ShipperID] = " & intSearch)