ACC: Count Function Ignores Null Values

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you use a query to perform a count, Null values are ignored.

RESOLUTION

In Microsoft Access 7.0 and 97

In Microsoft Access 7.0 and 97, you can use the NZ() function as well as the suggestions below to return another specified value when a variant is Null; therefore the count is of all records.

The new SQL statement with the NZ() function would read:

    SELECT Column2,
    COUNT(NZ([Column2])) AS EXPR1
    FROM Table1
    GROUP BY Column2;

In Microsoft Access 1.x and 2.0

To count Null values, count on a primary key column, a column that contains unique values, a counter column, a dummy column where all records equal 1, or use Count(*) in the field to count all rows regardless of whether an individual field has Null values.

The new SQL statement would read:

     SELECT Column2,
     COUNT(*)
     FROM Table1
     GROUP BY Column2;

STATUS

This behavior is by design with all aggregate (totals) functions.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create Table1 with two text columns as follows:

         Column1    Column2
         -------    -------
          foos
          ball
          foosball   junk
          null
          notnull    junk
   
   

2. Open a new query based on Table1.

3. On the View menu, click SQL. Enter the following SQL query:

         SELECT Column2, COUNT(Column2)
         FROM Table1
         GROUP BY Column2;
   

4. Run the query. Note that the result of the query is as follows:

         Column2    Count(Column2)
         -------    --------------
                          0
          junk            2
   
   

REFERENCES