Count Function (SQL Only)
See Also Example
Calculates the number of selected records in a query.
Count( expr )
The Count function uses the following argument.
expr String expression 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 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.
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 doesn't matter what values are stored in the records.
The Count function doesn't count records that have Null fields unless expr is the wildcard character (*). If you use the wildcard character, Count calculates the total number of records, including those that contain Null fields. Do not enclose the wildcard character in quotation marks. The following example calculates the number of records in the Orders table.
SELECT Count(*) 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 isn't counted. Separate the field names with an ampersand (&). The following example shows how you can limit the count to records in which either Order Amount or Freight is not Null.
SELECT Count("[Order Amount] & [Freight]") FROM Orders