Count Function

Calculates the number of records returned by a query.

Syntax

Count(expr)

The expr placeholder represents a string expression (string expression: An expression that evaluates to a sequence of contiguous characters. Elements of the expression can be: functions that return a string or a string Variant (VarType 8); a string literal, constant, variable, or Variant.) identifying the field that contains the data you want to count or an expression that performs a calculation using the data in the field. Operands in expr can include 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 kind of data, including text.

Remarks

 Tip    Try Office 2010 In Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires.
Watch a video or try Office 2010.

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 perform a calculation on a field, Count simply tallies the number of records. It does not matter what values are stored in the records.

The Count function does not count records that have Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) fields unless expr is the asterisk (*) wildcard character (wildcard characters: Characters used in queries and expressions to include all records, file names, or other items that begin with specific characters or that match a certain pattern.). If you use an asterisk, Count calculates the total number of records, including those that contain Null fields. Count(*) is considerably 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. Separate the field names with an ampersand (&). The following example shows 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 also use this expression in the SQL (Structured Query Language (SQL): A database query and programming language widely used for accessing, querying, updating, and managing data in relational database systems.) property of a QueryDef (QueryDef: A stored definition of a query in an Access database, or a temporary definition of a query in an ODBCDirect workspace.) object or when creating a Recordset (recordset: The collective name given to table-, dynaset-, and snapshot-type Recordset objects, which are sets of records that behave as objects.) object based on an SQL query.

 
 
Applies to:
Access 2007