Provided by Allen Browne, August 2006. Updated September 2007.
The built-in function - DCount() - cannot count the number of distinct values. The domain aggregate functions in Access are also quite inefficient.
ECount() offers an extra argument so you can count distinct values. The other arguments are the same as DCount().
Paste the code below into a standard module. To verify Access understands it, choose Compile from the Debug menu (in the code window.) In Access 2000 or 2002, you may need to add a reference to the DAO library.
You can then use the function anywhere you can use DCount(), such as in the Control Source of a text box on a form or report.
Use square brackets around your field/table name if it contains a space or other strange character, or starts with a number.
These examples show how you could use ECount() in the Immediate Window (Ctrl+G) in the Northwind database:
|? ECount("*", "Customers")||Number of customers.|
|? ECount("Fax", "Customers")||Number of customers who have a fax number.|
|? ECount("*", "Customers", "Country = 'Spain'")||Number of customers from Spain.|
|? ECount("City", "Customers", "Country = 'Spain'", True)||Number of Spanish cities where we have customers.|
|? ECount("Region", "Customers")||Number of customers who have a region.|
|? ECount("Region", "Customers", ,True)||Number of distinct regions|
|? ECount("*", "Customers", "Region Is Null")||Number of customers who have no region.|
You cannot embed a reference to a form in the arguments. For example, this will not work:
? ECount("*", "Customers", "City = Forms!Customers!City")
Instead, concatenate the value into the string:
? ECount("*", "Customers", "City = """ & Forms!Customers!City & """")
If you need help with the quotes, see Quotation marks within quotes.
Public Function ECount(Expr As String, Domain As String, Optional Criteria As String, Optional bCountDistinct As Boolean) As Variant On Error GoTo Err_Handler 'Purpose: Enhanced DCount() function, with the ability to count distinct. 'Return: Number of records. Null on error. 'Arguments: Expr = name of the field to count. Use square brackets if the name contains a space. ' Domain = name of the table or query. ' Criteria = any restrictions. Can omit. ' bCountDistinct = True to return the number of distinct values in the field. Omit for normal count. 'Notes: Nulls are excluded (whether distinct count or not.) ' Use "*" for Expr if you want to count the nulls too. ' You cannot use "*" if bCountDistinct is True. 'Examples: Number of customers who have a region: ECount("Region", "Customers") ' Number of customers who have no region: ECount("*", "Customers", "Region Is Null") ' Number of distinct regions: ECount("Region", "Customers", ,True) Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSql As String 'Initialize to return Null on error. ECount = Null Set db = DBEngine(0)(0) If bCountDistinct Then 'Count distinct values. If Expr <> "*" Then 'Cannot count distinct with the wildcard. strSql = "SELECT " & Expr & " FROM " & Domain & " WHERE (" & Expr & " Is Not Null)" If Criteria <> vbNullString Then strSql = strSql & " AND (" & Criteria & ")" End If strSql = strSql & " GROUP BY " & Expr & ";" Set rs = db.OpenRecordset(strSql) If rs.RecordCount > 0& Then rs.MoveLast End If ECount = rs.RecordCount 'Return the number of distinct records. rs.Close End If Else 'Normal count. strSql = "SELECT Count(" & Expr & ") AS TheCount FROM " & Domain If Criteria <> vbNullString Then strSql = strSql & " WHERE " & Criteria End If Set rs = db.OpenRecordset(strSql) If rs.RecordCount > 0& Then ECount = rs!TheCount 'Return the count. End If rs.Close End If Exit_Handler: Set rs = Nothing Set db = Nothing Exit Function Err_Handler: MsgBox Err.Description, vbExclamation, "ECount Error " & Err.Number Resume Exit_Handler End Function
The code builds a query statement that retrieves the records from the domain, limited to the criteria.
If you ask for a distinct count, it excludes nulls, groups by the expression, and returns the record count. If you did not ask for a distinct count, the recordset contains only the one record which gives the count.
Like DCount(), it returns the count of all records if you use the wildcard. If you specify a field name it returns the count of records that have something in this field (i.e. non-null.) If you want to include nulls, using "*" as the first argument will be more efficient.
Using the wildcard and asking for a distinct count makes no sense, so the function returns Null. It also returns Null if an error occurs, e.g. if you use a field name that does not exist.
Nulls are also excluded when you ask for a distinct count. This is consistent with the non-distinct count, and avoids the bug where Access handles DISTINCT inconsistently, depending on whether the field is indexed or not.
|Home||Index of tips||Top|