Does anyone know if access provides online documentation for dlookup?
I am using access 2002
What follows my name below is all I get (with no reference to dlookup) when
i search for dlookup.
Thanks
Fred
Dlookup results from within Access: (???)
Troubleshoot expressions
Unexpected results
Although using expressions is relatively straightforward, you can
occasionally get unexpected results when you:
Combine (concatenate) control or field values, variables, or text in an
expression.
Perform operations on fields that have blank values.
Refer to a subform or subreport property or control.
Refer to a column in a list box or combo box
Expression Builder
I can't find a field name in the Expression Builder.
I can't find the object or function I want in the Expression Builder.
You need to search for functions in VBA help, not in Access help.
Open any code window,then click on Help and type DLookUp.
Anyway, here it is.
If you have a specific question, post back.
DLookup Function
See Also Specifics
You can use the DLookup function to get the value of a particular
field from a specified set of records (a domain). Use the DLookup
function in Visual Basic, a macro, a query expression, or a calculated
control on a form or report.
You can use the DLookup function to display the value of a field that
isn't in the record source for your form or report. For example,
suppose you have a form based on an Order Details table. The form
displays the OrderID, ProductID, UnitPrice, Quantity, and Discount
fields. However, the ProductName field is in another table, the
Products table. You could use the DLookup function in a calculated
control to display the ProductName on the same form.
DLookup(expr, domain, [criteria])
The DLookup function has the following arguments.
Argument Description
expr An expression that identifies the field whose value you want to
return. It can be a string expression identifying a field in a table
or query, or it can be an expression that performs a calculation on
data in that field. In expr, you can include the name of a field in a
table, a control on a form, a constant, or a function. If expr
includes a function, it can be either built-in or user-defined, but
not another domain aggregate or SQL aggregate function.
domain A string expression identifying the set of records that
constitutes the domain. It can be a table name or a query name for a
query that does not require a parameter.
criteria An optional string expression used to restrict the range of
data on which the DLookup function is performed. For example, criteria
is often equivalent to the WHERE clause in an SQL expression, without
the word WHERE. If criteria is omitted, the DLookup function evaluates
expr against the entire domain. Any field that is included in criteria
must also be a field in domain; otherwise, the DLookup function
returns a Null.
Remarks
The DLookup function returns a single field value based on the
information specified in criteria. Although criteria is an optional
argument, if you don't supply a value for criteria, the DLookup
function returns a random value in the domain.
If no record satisfies criteria or if domain contains no records, the
DLookup function returns a Null.
If more than one field meets criteria, the DLookup function returns
the first occurrence. You should specify criteria that will ensure
that the field value returned by the DLookup function is unique. You
may want to use a primary key value for your criteria, such as
[EmployeeID] in the following example, to ensure that the DLookup
function returns a unique value:
Dim varX As Variant
varX = DLookup("[LastName]", "Employees", "[EmployeeID] = 1")
Whether you use the DLookup function in a macro or module, a query
expression, or a calculated control, you must construct the criteria
argument carefully to ensure that it will be evaluated correctly.
You can use the DLookup function to specify criteria in the Criteria
row of a query, within a calculated field expression in a query, or in
the Update To row in an update query.
You can also use the DLookup function in an expression in a calculated
control on a form or report if the field that you need to display
isn't in the record source on which your form or report is based. For
example, suppose you have an Order Details form based on an Order
Details table with a text box called ProductID that displays the
ProductID field. To look up ProductName from a Products table based on
the value in the text box, you could create another text box and set
its ControlSource property to the following expression:
=DLookup("[ProductName]", "Products", "[ProductID] =" _
& Forms![Order Details]!ProductID)
Tips
Although you can use the DLookup function to display a value from a
field in a foreign table, it may be more efficient to create a query
that contains the fields that you need from both tables and then to
base your form or report on that query.
You can also use the Lookup Wizard to find values in a foreign table.
Note Unsaved changes to records in domain aren't included when you
use this function. If you want the DLookup function to be based on the
changed values, you must first save the changes by clicking Save
Record on the Records menu, moving the focus to another record, or by
using the Update method.
Example
The following example returns name information from the CompanyName
field of the record satisfying criteria. The domain is a Shippers
table. The criteria argument restricts the resulting set of records to
those for which ShipperID equals 1.
Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = 1")
The next example from the Shippers table uses the form control
ShipperID to provide criteria for the DLookup function. Note that the
reference to the control isn't included in the quotation marks that
denote the strings. This ensures that each time the DLookup function
is called, Microsoft Access will obtain the current value from the
control.
Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = " _
& Forms!Shippers!ShipperID)
The next example uses a variable, intSearch, to get the value.
Dim intSearch As Integer
Dim varX As Variant
intSearch = 1
varX = DLookup("[CompanyName]", "Shippers", _
"[ShipperID] = " & intSearch)
