Table of contents
TOC
Collapse the table of content
Expand the table of content

Count Function (Microsoft Access SQL)

office 365 dev account|Last Updated: 6/12/2017
|
1 Contributor

Applies to: Access 2013 | Access 2016

In this articleSyntaxRemarks Example

Calculates the number of records returned by a query.

Syntax

Count(expr)

The expr placeholder represents a 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 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

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 or region.

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 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 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 property of a QueryDef object or when creating a Recordset object based on an SQL query.

Example

This example uses the Orders table to calculate the number of orders shipped to the United Kingdom.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub CountX() 

    Dim dbs As Database, rst As Recordset 

    ' Modify this line to include the path to Northwind 
    ' on your computer. 
    Set dbs = OpenDatabase("Northwind.mdb") 

    ' Calculate the number of orders shipped  
    ' to the United Kingdom. 
    Set rst = dbs.OpenRecordset("SELECT" _ 
        &; " Count (ShipCountry)" _ 
        &; " AS [UK Orders] FROM Orders" _ 
        &; " WHERE ShipCountry = 'UK';") 

    ' Populate the Recordset. 
    rst.MoveLast 

    ' Call EnumFields to print the contents of the  
    ' Recordset. Pass the Recordset object and desired 
    ' field width. 
    EnumFields rst, 25 

    dbs.Close 

End Sub 

ACCESS SUPPORT RESOURCES
Access for developers forum on MSDN
Access help on support.office.com
Access help on answers.microsoft.com
Search for specific Access error codes on Bing
Access forums on UtterAccess
Access wiki on UtterAcess
Access developer and VBA programming help center (FMS)
Access posts on StackOverflow

© 2017 Microsoft