ACC: Count Function Ignores Null Values

ID: Q92747

The information in this article applies to:

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

Create Table1 with two text columns as follows:


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

Open a new query based on Table1.

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

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

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


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

REFERENCES

For more information about counts in queries, search the Help Index for "Count Function," and then view the available topics, or ask the Microsoft Access 97 Office Assistant.

Keywords : kbusage QryOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb