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 recordset that identifies the domain.
It can be a table or a query.
The
Criteria argument is an optional string expression that you can use to
restrict the range of the data on which the
DLookup() function is performed. Note that the
criteria argument is identical to the WHERE clause in a 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 enter the examples in
the
ControlSource property of a text box on a form or a 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.
Functions 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 evaluated, the individual pieces of the criteria are evaluated first and
appended or concatenated, and 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 forms reference (as in the second
example above), Microsoft Access assumes 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 forms 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] & "'")
=DLookup("[ContactName]", "[Customers]", _
"[CustomerID]='" & Forms![Orders]![CustomerID] & "'")
In the second example, the criteria is created by concatenating
three string expressions. 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. The whole value is then 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
specified date, use the following sample criteria:
=DLookup("[LastName]", "Employees", "[BirthDate] = #12-08-68#")
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 (text), 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:
Sub subTest
'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
End Sub
If the
DLookup() function is unsuccessful, the variable
Result contains a Null.
Note that the criteria is composed of
four pieces that are evaluated individually. The results are appended and
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 2/12/2001, 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 test the components individually within 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 can be used to help
debug Visual Basic modules. Use the Immediate Window to test and evaluate
expressions independently of the form or macro where the expression is used.
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 text.
To
troubleshoot this expression, try the following:
- Press CTRL+G to open the Immediate window.
- Test the function with no criteria. To do so, type the
following in the Immediate Window, and then press ENTER:
? DLookup("[OrderID]", "Orders")
- Verify that the forms references are correct. To do so,
open the Orders form in the sample database Northwind.mdb, and enter each of
the following lines in the Immediate Window. Press ENTER after each line.
? Forms![MyForm]![CustomerID]
? 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 press ENTER after each:
? DLookup("[OrderID]", "Orders", "[CustomerID] = 'SIMOB'")
? 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 press ENTER after each one:
? DLookup("[OrderID]", "Orders", _
"[CustomerID] = '" & Forms![MyForm]![CustomerID] & "'")
? DLookup("[OrderID]", "Orders", _
"[EmployeeID] = " & Forms![MyForm]![EmployeeID])