Microsoft KB Archive/92747

= ACC: Count Function Ignores Null Values =

Article ID: 92747

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q92747



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



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



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.



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 </li></ol>

<div class="references_section">