Using DLookUp
Printer Friendly 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.
SELECT "FieldName"
FROM "TableName"
WHERE "Criteria=n"
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.]
DLookup("StateName", "tblStates", "StateCode = '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.
Private Sub cboState_AfterUpdate()
txtStName = DLookup("StateName", "tblStates", "StateAbbr = '" + cboState + "'")
End Sub
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:
DLookup("FieldName", "TableName", "Criteria='n'")
The syntax for a
NUMERIC value is:
DLookup("FieldName", "TableName", "Criteria=n")
The syntax for a
DATE value is:
DLookup("FieldName", "TableName", "Criteria=#date#")
Lookup can look-up values from
FORM CONTROLS:
DLookup("FieldName", "TableName", "Criteria = " + forms!FormName!ControlName)
For
STRING values:
DLookup("FieldName", "TableName", "Criteria = '" + forms!FormName!ControlName + "'")
For
DATE values:
DLookup("FieldName", "TableName", "Criteria=#" + forms!FormName!ControlName3 + "#")
Multiple Criteria
DLookup("FieldName", "TableName", "Criteria1 = " + forms!FormName!ControlName1 _
+ " AND Criteria2 = '" + forms!FormName!ControlName2 + "'" _
+ " AND Criteria3 = #" + forms!FormName!ControlName3 + "#" )