You can use the DCount function to determine the number of records that are in a specified set of records (domain). Use DCount in a macro or module, in a query expression, or in a calculated control.
For example, you could use DCount in a module to return the number of records in an Orders table that correspond to orders placed on a particular date.
DCount(expr, domain[, criteria])
The DCount function uses the following arguments.
| Part | Description |
| expr | Expression that identifies the field for which you want to count records. 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. You can include in expr the name of a table field, 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. |
| Part | Description |
| domain | String expression identifying the set of records that constitutes the domain. It can be a table name or a query name. |
| criteria | Optional string expression used to restrict the range of data on which DCount is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, DCount evaluates expr against the entire domain. Any field included in criteria must also be a field in domain; otherwise DCount returns a Null. |
DCount finds the number of records in the field or expression defined by expr in the table or query referred to by domain, according to any restrictions specified by criteria.
Use DCount to count the number of records in a domain when you don’t need to know their particular values. Although the expr argument can perform a calculation on a field, DCount simply tallies the number of records. The value of any calculation performed by expr is unavailable.
Whether you use DCount 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.
Use DCount in a calculated control when you need to specify criteria to restrict the range of data on which the function is performed. For example, to display the number of orders to be shipped to California, set the ControlSource property of a text box to the following expression:
= DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")
If you simply want to count all records in domain without specifying any restrictions, use the Count function.
Tip The Count function has been optimized to speed counting of records in queries. Use Count in a query expression instead of DCount, and set optional criteria to enforce any restrictions on the results. Use DCount when you must count records in a domain from within a code module or macro, or in a calculated control.
You can use DCount to count the number of records containing a particular field that is not in the record source on which your form or report is based. For example, you could display the number of orders in the Orders table in a calculated control on a form based on the Products table.
The DCount function doesn’t count records that contain Null values in the field referenced by expr, unless expr is the asterisk (*) wildcard character. If you use an asterisk, DCount calculates the total number of records, including those that contain Null fields. The following example calculates the number of records in an Orders table.
intX = DCount("*", "Orders")
If domain is a table with a primary key, you can also count the total number of records by setting expr to the primary key field, since there will never be a Null in the primary key field.
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, DCount returns records containing a Null value as well as records containing data. If you use the addition operator, DCount returns only records containing data. The following example demonstrates the effects of each operator when used with a field that contains no Null values (ShipName) and a field that contains Null values (ShipRegion).
intW = DCount("[ShipName]", "Orders") ' Returns 1078.= DCount("[ShipRegion]", "Orders") ' Returns 834.= DCount("[ShipName] + [ShipRegion]", "Orders") ' Returns 834.= DCount("[ShipName] & [ShipRegion]", "Orders") ' Returns 1078.
Note The ampersand (&) is the preferred operator for performing string concatenation. You should avoid using the addition operator (+) for anything other than numeric addition, unless you specifically wish to propagate nulls through an expression.
Unsaved changes to records in domain are not 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 on the File menu, moving the focus to another record, or using the Update method.
DAvg Function, Domain Aggregate Functions, DSum Function.
This example returns the number of orders shipped to the United Kingdom after January 1, 1995. The domain is an Orders table. The criteria argument restricts the resulting set of records to those for which ShipCountry equals UK and ShippedDate is greater than 1-1-95.
intX = DCount("[ShippedDate]", "Orders", _
"[ShipCountry] = 'UK' AND [ShippedDate] > #1-1-95#")
In the next example, the criteria argument includes the current value of a combo box called ShipCountry. The values in the ShipCountry combo box come from the field ShipCountry in an Orders table. Note that the reference to the control is not included in the quotation marks that denote the strings. This ensures that each time the DCount function is called, Microsoft Access will obtain the current value from the control.
intX = DCount("[ShippedDate]", "Orders", "[ShipCountry] = '" _
& Forms!Orders![ShipCountry] & "'AND [ShippedDate] > #1-1-95#")