ACC: Count Function Ignores Null Values (92747)

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;

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.