Novice: Requires knowledge of the user interface on single-user computers.
MORE INFORMATION
The DLookup() Function Syntax and Usage
The DLookup() function can be used in an expression or in a user-defined
function to return a field value in a domain or a specified set of records.
The syntax of the DLookup() function is as follows:
DLookup(Expression, Domain [, Criteria] )
The function has three arguments: the expression, the domain, and the
criteria. (Note that the criteria argument is optional.)
The expression argument is used to identify the field that contains the
data in the domain that you want returned or is used 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 name.
The criteria argument is an optional string expression that is used 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, the DLookup() function
returns a null.
DLookup() Function Examples
The following examples demonstrate how to use the DLookup() function to
find or return values from a table or query. These examples refer to the
sample database NWIND.MDB and can be entered in the ControlSource property
of a text box on a form or report.
NOTE: In the following sample code, 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 this code.
A Function with No Criteria
This example demonstrates how to use the DLookup() function without any
criteria specified. This example returns the value contained in the Last
Name field of the first record in the Employees table:
=DLookUp("[Last Name]", "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("[Last Name]", "Employees", "[Employee ID] = 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 example above), 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 examples on the Orders form in the sample
database NWIND.MDB:
=DLookUp("[Last Name]", "Employees", _
"[Employee ID] = Form![Employee ID]")
=DLookUp("[Last Name]", "Employees", _
"[Employee ID] = " & [Employee ID])
=DLookUp("[Last Name]", "Employees", _
"[Employee ID] = " & Forms![Orders]![Employee ID])
The three examples above return the same results.
In the first example, Form![Employee ID] appears inside the criteria's
quotation marks. "Form" tells Microsoft Access that the field reference
"Employee ID" comes from the current form. If you omit it, Microsoft
Access compares Employee ID 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 Employee ID field, so the
argument
"[Employee ID] = [Employee ID]"
computes to
"1 = 1"
and would therefore be the first record that matched the criteria.
The criteria for the second 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, the individual pieces of the criteria
are first evaluated, and then appended or concatenated. Then, the whole
value is computed.
If the current value in the Employee ID field on the Orders form is 7, the
original criteria expression
"[Employee ID] = " & [Employee ID]
computes to
"[Employee ID] = " & 7
which, when concatenated, computes to:
"[Employee ID] = 7"
If you do not specify the full form reference (which the second example
above does not), Microsoft Access assumes that you are referring to the
current form.
The following example is a derivative of the third example above:
=DLookUp("[Last Name]", "Employees", _
"[Employee ID] = Forms![Orders]![Employee ID]")
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 Employee ID 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 then pressing F9.
If you want the field to automatically update 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 Employee ID text box.
Specifying Textual Criteria
All the examples above demonstrate how to use the DLookup() function with
numeric criteria. If the criteria fields are textual, enclose the text in
single quotation marks, as in the following example:
=DLookUp("[Title]", "Employees", "[Last Name] = '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", "[Last Name] = ""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 Customer ID field is a textual key field
for the criteria, so the DLookup() statement is
=DLookup("[Contact Name]", "Customers", _
"[Customer ID]='" & [Customer ID]& "'")
-or-
=DLookup("[Contact Name]", "Customers", _
"[Customer ID]='" & Forms![Orders]![Customer ID]& "'")
In the second example, the criteria is made up by concatenating three
string expression pieces. The first is "[Customer ID]= '", the second is
the value contained in the actual Customer ID field, and the third is the
closing single quotation mark enclosed in double quotation marks.
When this criteria is being evaluated, the individual pieces are first
evaluated, and then their results appended or concatenated. Then the whole
value is computed.
If the current value selected in the Customer ID combo box on the Orders
form is Alfreds Futterkiste, the bound column for the combo box returns
ALFKI as the Customer ID. The original criteria expression
"[Customer ID]= '" & [Customer ID]& "'"
computes to
"[Customer ID]= '" & "ALFKI" & "'"
which, when concatenated, computes to:
"[Customer ID]= '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 given
date, use the following sample criteria:
=DLookUp("[Last Name]", "Employees", "[Birth Date] = #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 Order ID for one of the orders sold by employee "Andrew
Fuller", with an Employee ID of 2 (numeric), for customer "Simons bistro",
with a Customer ID of SIMOB (textual), use the following sample DLookup()
statement:
=DLookUp("[Order ID]", "Orders", _
"[Customer ID]= 'SIMOB' And [Employee ID] = 2")
This statement returns Order ID 10556, which is the first Order ID that
matches the criteria. Order ID 10669 also matches the criteria.
The example above uses hard-coded (or specific) Customer ID and Employee ID
values. To use variables instead of specifics for the criteria, you can
use Access Basic to concatenate multiple string expressions. The following
Access 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("[Order ID]", "Orders", _
"[Customer ID]= '" & CustID & "' And [Employee ID] = " & EmpID)
MsgBox Result
If the DLookup() function is unsuccessful, the variable Result contains
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
"[Customer ID]= '" & CustID & "' And [Employee ID] = " & EmpID
computes to
"[Customer ID]= '" & "SIMOB" & "' And [Employee ID] = " & 2
which, when concatenated, computes to:
"[Customer ID]= 'SIMOB' And [Employee ID] = 2"
You can use the next example to find an employee whose birthday falls on
today's date:
=DLookUp("[Last Name]", "Employees", _
"Month([Birth Date]) = " & Month(Date) & " And Day([Birth Date]) _
= " & Day(Date))
If today's date is 12/2/93, the original criteria expression:
"Month([Birth Date]) = " & Month(Date) & " And Day([Birth Date]) = " _
& Day(Date)
computes to
"Month([Birth Date]) = " & 12 & " And Day([Birth Date]) = " & 2
which, when concatenated, computes to:
"Month([Birth Date]) = 12 And Day([Birth Date]) = 2"
Tips for Troubleshooting and Debugging DLookup() Expressions
To troubleshoot expressions, break down the expression into smaller
components and test the components individually in the Immediate window to
ensure they work correctly. If the smaller components work correctly, they
can be put back together, piece by piece, until the final expression works
correctly.
The Immediate window is a tool that you can use to help debug Access Basic
modules. You can 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 you can use to break down a
DLookup() expression into smaller components that you can test in the
Immediate window. Assume you are having difficulty with the following
statement:
=DLookUp("[Order ID]", "Orders", _
"[Customer ID]= '" & Forms![MyForm]![Customer ID] _
& "' And [Employee ID] = " & Forms![MyForm]![Employee ID])
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:
- Open or create a module. From the View menu, choose Immediate window.
- Try the function without any criteria. Type the following line in the
Immediate window, and then press ENTER:
? DLookUp("[Order ID]", "Orders")
Microsoft Access will perform the calculation and display the result on
the next line in the Immediate window.
- Make sure the form references data correctly data. Type each of the
following lines in the Immediate window, and then press ENTER:
? Forms![MyForm]![Customer ID]
-and-
? Forms![MyForm]![Employee ID]
- Try to make the criteria fields work independently by hard coding
values into the expression. Type each of the following lines in the
Immediate window, and then press ENTER:
? DLookUp("[Order ID]", "Orders", "[Customer ID]= 'SIMOB'")
-and-
? DLookUp("[Order ID]", "Orders", "[Employee ID] = 2")
- Try to make the criteria fields work independently with a parameter in the query. Type each of the following lines in the Immediate window, and
then press ENTER:
? DLookUp("[Order ID]", "Orders", _
"[Customer ID]= '" & Forms![MyForm]![Customer ID]& "'")
-and-
? DLookUp("[Order ID]", "Orders", _
"[Employee ID] = " & Forms![MyForm]![Employee ID])