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 record set that identifies the
domain. It can be a table or a query name.
The
criteria argument is an optional string expression that you can use to
restrict the range of the data that the
DLookup() function is performed on. Note that the
criteria argument is identical to the WHERE clause in an 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 type the examples in
the
ControlSource property of a text box on a form or 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.
A Function 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 being evaluated, first the individual pieces of the criteria are
evaluated and appended or concatenated; 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 form reference (as in the second
example above), Microsoft Access assumes that 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 form 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 made up by concatenating
three string expression pieces. 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; then the whole value is 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
particular date, use the following sample criteria:
=DLookUp("[LastName]", "Employees", "[BirthDate] = #01-27-66#")
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 (textual), 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:
' 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
If the
DLookup() function is unsuccessful, the variable Result contains a
Null.
Note that the criteria is made up of four pieces that
are evaluated individually. The results are appended, and then 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 12/2/93, 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 then test the components individually in 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 is a tool that you
can use to help debug Visual Basic modules. Use the Immediate window to test
and evaluate expressions independently of the form or macro the expression is
to be used in. 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 textual.
To
troubleshoot this expression, try the following:
- Press CTRL+G to open the Immediate window.
- Try the function without any criteria. To do so, type the
following in the Immediate window, and then press ENTER:
? DLookUp("[OrderID]", "Orders")
- Make sure the form references are correct. To do so, open
the Orders form in the sample database Northwind.mdb and type each of the
following lines in the Immediate window, and then press ENTER:
? 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 then press ENTER:
? 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 then press ENTER:
? DLookUp("[OrderID]", "Orders", _
"[CustomerID] = '" & Forms![MyForm]![CustomerID] & "'")
? DLookUp("[OrderID]", "Orders", _
"[EmployeeID] = " & Forms![MyForm]![EmployeeID])