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.

Tip
Data functions can be also used in a macro, a query expression, or a calculated control on a form or report.
You can use the Data functions 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 - arguments common to all Data functions

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

menu with dlookup and dcountThis code shows what is common to all Data retrieval functions:

Click this Start dlookup demo screencast to see the creation of dlookup code.open form demo

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'

Note
  • Any field that is included in criteria must also be a field in domain, otherwise, the Data function returns a Null.
  • single quotation marks (') are are added befor and after the string to prevent confusion between Field names and Value strings.
  • The use of wildcards is supported
  • Multiple fields in criteria are separated by the keyword AND or OR
  • The use of more than 1 fieldvalue to evaluate is possible, also you can set more than 1 fieldvalues in 1 result.
Warning when using DLookup
  • If criteria is omitted, the DLookup function evaluates expr against the entire domain, the result is then unpredictable.
  • The criteria has also to point to a unique value, as you get only the first occurrence.

The use of wildcards

You can use wildcards in the Criteria argument:

Criteria:="CompanyName LIKE 'Blau*'

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. You may also consult 'Tips for Troubleshooting and Debugging DLookup() Expressions'

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.

Tip
  • If you use an aggregate function in a calculated control, you may want to place the control on the form header or footer so that the value for this control is not recalculated each time you move to a new record.
  • If the data type of the field from which expr is derived is a number, the aggregate function returns a Double data type. If you use the DAvg function in a calculated control, include a data type conversion function in the expression to improve performance.
  • Although you can use an aggregate function to determine the average of values in a field in a foreign table, it may be more efficient to create a query that contains all of the fields that you need and then base your form or report on that query.
Warning
Unsaved changes to records in domain aren't included when you use this function. If you want the DCount function to be based on the changed values, you must first save the changes by clicking Save Record under Records on the Data tab, moving the focus to another record, or by using the Update method.

DCount - Count records

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 & "#")
Tip
The Count function has been optimized to speed counting of records in queries. Use the Count function in a query expression instead of the DCount function, and set optional criteria to enforce any restrictions on the results. Use the DCount function when you must count records in a domain from within a code module or macro, or in a calculated control.

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 (+):

DSum

Calculate the sum of a set of values in a specified set of records

dblSumFreightCA = DAvg(Expr:="[Freight]", Domain:="Orders", "Criteria:=[ShipRegion] = 'CA'")

DAvg - Calculate average for specified field

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

dblAverageFreightCA = DAvg(Expr:="[Freight]", Domain:="Orders", "Criteria:=[ShipRegion] = 'CA'")

DMax and DMin

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

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

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

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.

Warning
If the query does not include an ORDER BY clause, the values returned will be arbitrary because records are usually returned in no particular order.