MS Access DCount Function
The MS Access DCOUNT function is used to count the number of records in a specified set of records (a domain). Besides, it can be used in VBA code, a macro, a query expression, or a calculated control. The DCOUNT function takes three arguments: expr, domain, and criteria. However, the expr argument can be a field name or a calculation on a field. The domain argument can be a table name or a query name. Finally, the criteria argument can be an optional expression that limits the records counted.
For example, you can use the DCOUNT function in a module to return the number of countries in countries table that corresponds to a specific continent.
Expression Syntax
DCount ( expr , domain, [criteria] )
VBA Syntax
intX =DCount("*", "tblCountries", "[Continent] = 'Asia'")
Where intX is the word dimension for integer. However, the DCount function doesn’t count records that contain Null values unless the expression is determined by the asterisk (*) character. So, to count all records in a specific table or query, you can use the below VBA Code:
intX = DCount("*", "tblCountries")
Argument and Descriptions of MS Access DCount Function
- Expr: Required. An expression that identifies the field for which you want to count records.
- Domain: Required. A string expression identifies the set of records that constitutes the domain.
- Criteria: Optional. A string expression is used to restrict the range of data on which the DCount function is performed.
In our downloadable sample, we used the DCount Function to count the number of countries in each continent using the below syntax:
Me.txtDCount = DCount("*", "tblCountries", "Continent='" & Me.cboContinent & "'")