Count Function

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

Calculates the amount of records returned by a query.


Count( expr )

The expr placeholder denotes a string expression detecting the field that occupies your sought data to count or an expression that conducts a calculation using the data in the field. Operands in expr can contain the name of a table field or function (which can be either intrinsic or user-defined but not other SQL aggregate functions ). You can count any type of data, including text.


Tip: In Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires. 

You can use Count to count the number of records in an underlying query. For example, you could use Count to count the number of orders shipped to a particular country.

Although expr can run a calculation on a field, Count only tallies the number of records. It does not matter what values are stored in the records.

The Count function omits counting records that contain Null fields unless expr is the asterisk (*) wildcard character. If you use an asterisk, Count calculates the total number of records, including those that contain Null fields. Count(*) is substantially faster than Count([Column Name]). Do not enclose the asterisk in quotation marks (‘ ‘). The following example calculates the number of records in the Orders table:

SELECT Count(*) AS TotalOrders FROM Orders;

If expr identifies multiple fields, the Count function counts a record only if at least one of the fields is not Null. If all of the specified fields are Null, the record is not counted. Split the field names with an ampersand (&). The following example displays how you can limit the count to records in which either ShippedDate or Freight is not Null:

SELECT Count(‘ShippedDate & Freight’) AS [Not Null] FROM Orders;

You can use Count in a query expression. You can even apply this expression in the SQL property of a QueryDef object or when making a Recordset object based on an SQL query.

See Also

Count data by using a query

Calculating Fields in SQL Functions

This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.

Leave a Reply

%d bloggers like this: