Data retrieval functions, DLookup, DCount, DSum and more

You can use the Data retrieval functions such as DLookUp in an expression or in a VBA function to return a field value in a table. The first section focuses on the DLookup function and it's arguments Expr, Domain and Criteria. This information roughly also applies to aggregate (totals) functions such as DSum and DCount which are discussed in the later sections.

Dlookup - arguments common to all Data functions

Dim strCompanyName As String
strCompanyName = Nz(Value:=DLookup(Expr:="CompanyName", Domain:="Customers",
                                        Criteria:="CustomerID='BLAUS'

This code shows what is common to all Data retrieval functions:

• Expr: the field to take the value from
• Domain: which table or query to retrieve a record from
• Criteria: which record the value will be taken from
• The DLookup function returns a variant because 1. the type of returned value depends on the field it was taken from and 2. if no record exists then Null will be returned.
• By applying the Nz function to the result, the Null will automatically converted to the required type - here String.

Click this to see the creation of dlookup code.

Expr - Expression

The common use of the Expr argument is to return the value of a field from the Domain. However as the argument name Expr suggests it can do more and this is indeed so:
Expr:="'Best company: ' & CompanyName & ' from ' & ContactName"
would returnBest company: Blauer See Delikatessen from Hanna Moos, combining the value of two fields (CompanyName and ContactName) and two texts.

Expr can be 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 control on a form, a constant, or a function both built-in and user-defined.

Domain

The Domain argument identifies the specified set of records (domain). It can be a table name or a query name for a query that does not require a parameter. You may not use an SQL expression here.

Criteria

The Criteria argument restricts the range of data on which the Data retrieval function is performed. Criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE, in the above example: Criteria:="CustomerID='BLAUS'

The use of wildcards

You can use wildcards in the Criteria argument:

If you test this line in the VBA debugger, don't forget to remove the extra quotes.

It may be difficult to make the right criteria and to code them correctly.

With Dlookup it is not possible to specify a sort order. If that is a concern, one approach is to use Allen Browne's DLookup replacement instead: Extended DLookup(). His function allows you to specify a custom sort order.

Available aggregate functions DCount, DSum, DAvg and more

In the sections below the aggregate functions are discussed: DCount, DSum, DAvg, DMax and DMin, StDev and StDevP, DVar and DVarP, DFirst and DLast

The meaning and use of the aggregate functions arguments Expr, Domain and Criteria has beeen discussed in the previous section.

DCount - returns the number of records from an Access table (or domain).

Used to determine the number of records, when you don't need to know their particular values.

 intOrdersCountInRegionAfterDate = DCount("[ShippedDate]", "Orders", _
                                    "[ShipCountryRegion] = '" & strCountryRegion & _
                                    "' AND [ShippedDate] > #" & dteShipDate & "#")

The DCount function doesn't count records that contain Null values in the field referenced by Expr. There are two possible ways to ensure DCount returns all records:

1. Set Expr to the asterisk (*) wildcard character. intCountAllOrders = DCount("*", "Orders")
2. Set Expr to the primary key field - if available. There will never be a Null in the primary key field.

Count records excluding multiple Null fields - advanced handling

If expr identifies multiple fields, separate the field names with a concatenation operator, either an ampersand (&) or the addition operator (+):

• If you use an ampersand (&) to separate the fields, the DCount function returns the number of records containing data in any of the listed fields.
• If you use the addition operator (+), the DCount function returns only the number of records containing data in all of the listed fields.

DSum - calculate the sum of a set of values in a specified set of records

Calculate the sum of a set of numeric values from an Access table (or domain).

DAvg - calculate average for specified field

Use function DAvg to calculate the average of a set of numeric values in a specified set of records. Records containing Null values are not included.

DMax and DMin - returns the maximum (or minimum value) from an Access table (or domain)

You can use the DMax and DMin functions to determine the maximum and minimum values in a specified set of records (a domain).

If Expr concerns numeric data, the DMax and DMin functions return numeric values. If string data, they return the string that is first or last alphabetically. The DMin and DMax functions ignore Null values in the field referenced by expr. If no record satisfies criteria or if domain contains no records, the DMin and DMax functions return a Null.

DVar and DVarP - estimate variance

Use the DVarP function to evaluate variance across a population and the DVar function to evaluate variance across a population sample.

If domain refers to fewer than two records or if fewer than two records satisfy criteria, the DVar and DVarP functions return a Null, indicating that a variance can't be calculated.

StDev and StDevP - standard deviation

Return estimates of the standard deviation for a population or a population sample represented as a set of values contained in a specified field on a query.StDev returns estimates of the standard deviation for a population or a population sample represented as a set of values contained in a specified field on a query.

The StDevP function evaluates a population, and the StDev function evaluates a population sample. If the underlying query contains fewer than two records (or no records, for the StDevP function), these functions return a Null value (which indicates that a standard deviation cannot be calculated).

DFirst and DLast - returns the first/last value from an Access table (or domain).

These functions return the value of a specified field in the first or last record, respectively, of the result set returned by a query that includes an ORDER BY. For tables the returned value will be random.