DLookup Function (SQL Only)

See AlsovbfctDLookupSee                 ExamplevbfctDLookupEx>Low


Returns a field value in a specified set of records (domainCQNETL).


DLookup( expr, domain [, criteria] )


The DLookup function uses the following arguments.

Argument        Description


expr                 String expression1330R89 identifying the field that contains the data you want to return, or an expression that performs calculations using the data in that field.  Operands in expr can include the name of a table field, or a Visual Basic function (which can be intrinsic but not a user-defined function or one of the other domain aggregate or SQL aggregate functions).

domain             String expression identifying the records that constitute the domain.  It can be a table name, a query name, or an SQL expression29F05E5 that returns data.

criteria              Optional string expression used to restrict the range of data on which DLookup is performed.  For example, criteria could be the WHERE clause in an SQL expression (without the word WHERE).  If criteria is omitted, DLookup evaluates expr against the entire domain.


You can use DLookup to return a field value based on the value of another field.  In the following example, DLookup uses the Employee ID field to return the corresponding last name.

X = DLookup("[Last Name]", "Employees", "[Employee ID] = 24")


If the criteria argument contains non-numeric text other than field names, you must enclose the text in single quotation marks.  In the following example, Ship Postal Code is the name of a field, and 94117 is a string literal.

X = DLookup("[Ship Via]", "Orders", "[Ship Postal Code] = '94117'")

Y = DLookup("[Ship Via]", "Orders", "[Ship Via] = 1")


Even if more than one record satisfies criteria, DLookup returns only one field.  If no record satisfies criteria, or if domain contains no records, DLookup returns a Null.