DCount Function (SQL Only)
See Also Example
Returns the number of selected records in a specified set of records (domain).
DCount( expr, domain [, criteria] )
The DCount function uses the following arguments.
expr String expression identifying the field that contains the data you want to count, 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). You can count any kind of data, including text.
domain String expression identifying the records that constitute the domain. It can be a table name, a query name, or an SQL expression that returns data.
criteria Optional string expression used to restrict the range of data on which DCount is performed. For example, criteria could be the WHERE clause in an SQL statement (without the word WHERE). If criteria is omitted, DCount evaluates expr against the entire domain.
If you don't need to know particular values, you can use DCount to count the number of records in a domain. For example, you could use DCount to count the number of orders shipped to a particular region. Although expr can perform a calculation on a field, DCount simply tallies the number of records. It doesn't matter what values are stored in the records.
Unless expr is the asterisk (*) wildcard character, the DCount function doesn't count records that contain Null fields. 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 the Orders table.
X = DCount("*", "Orders")
If domain is an indexed table, you can also count the total number of records by setting expr to the primary key field. The primary key field in an indexed table will not be Null.
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 Country is the name of a field, and UK is a string literal.
X = DCount("[Freight]", "Orders", "[Ship Country] = 'UK'")
Y = DCount("[Freight]", "Orders", "[Ship Via] = 1")
If expr identifies multiple fields, separate the field names with either a plus sign (+) or an ampersand (&). The following examples show the effects of each when used with a non-Null field (Ship Name) and a Null field (Ship Region).
W = DCount("[Ship Name]", "Orders") ' Returns 1078.
X = DCount("[Ship Region]", "Orders") ' Returns 834.
Y = DCount("[Ship Name] + [Ship Region]", "Orders") ' Returns 834.
Z = DCount("[Ship Name] & [Ship Region]", "Orders") ' Returns 1078.