DLookup in Access

by Linda Quinn

Close Window        Print Page



DLookUp is an Access command that looks up data in a table or query.

DLookup is used in code for forms, reports or modules.


Syntax:     DLookup("Expression", "Domain", "Criteria=n")

Expression	Domain	Criteria
A fieldname or expression that performs a calculation on a field. An expression can be a field in a table, a control on a form, a constant or a function.	A Domain is the set of records to choose from in the expression. This is usually a table or a query.	The Criteria is optional. If omitted, all the values requested in the expression, that are in the domain, are returned. The criteria statement is like the "WHERE" statement in a SQL query statement without the word "where".

Usage:
    var = DLookup("FieldName", "TableName")
OR
    var = DLookup("FieldName", "TableName", "Criteria=n")

The DLookup arguments match the arguments of a SQL Select statement.

One use for DLookup is to get a value based on a selection from a list or combo box.

In this example, a state code is selected from a combo box and DLookup is used to retrieve the state name. [The combo box is named cboState.]

Notice that cboState is enclosed in single quotes.

This inserts the VALUE of cboState, instead of the WORD cboState.

This is necessary when the value being looked up is a STRING.

Sample code from an Access Form:

After selecting a state code from the Combobox cboStates, the After_Update event is raised.

This code sample captures the event and uses DLookup to get the state name from the table tblStates.

The above example says lookup the value of field StateName from the table tblStates where the field StateAbbr matches the value selected in the combo box cboState.

Notice that the word cboState is enclosed in single quotes within the quotes of the expression.

The syntax for a STRING value is:

The syntax for a NUMERIC value is:

The syntax for a DATE value is:

Lookup can look-up values from FORM CONTROLS:

For STRING values:

For DATE values:

Multiple Criteria