Count Function (SQL Only)

See AlsovbfctSQLCountSee                 ExamplevbfctSQLCountEx>Low


Calculates the number of selected records in a query.


Count( expr )


The Count function uses the following argument.

Argument        Description


expr                 String expression1330R89 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