ACC2000: Count Function Ignores Null Values

ID: Q201982

The information in this article applies to:
Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).


SYMPTOMS

When you use the Count function in a query, view, or stored procedure, Null values are ignored.


RESOLUTION

The resolution to this problem is different depending on whether you are working with a query in a Microsoft Access database (.mdb), or with views and stored procedures in a Microsoft Access project (.adp).

Access database (.mdb)

You can use the NZ() function to return another specified value when a variant is Null; therefore the count is of all records.

To create a query and use the NZ() function, follow these steps:
  1. Create Table1 with two text columns as follows:


    2. 
   Column1    Column2
   -------    -------
   apples
   oranges
   cherries   junk
   null
   notnull    junk
  2. Create the following query based on Table1:


    3. 
   Query: Query1
   -------------
   Type: Select Query

   Field: Column2
       Table: Table1
       Total: GroupBy
   Field: Expr1: NZ([Column2])
       Table: Table1
       Total: Count
  3. On the Query menu, click Run.

    Note that the result of the query is as follows:


    4. 
   Column2        Expr1
   -------        -----
                    3
    junk            2

Access project (.adp)

Access projects do not support the NZ() function in views and stored procedures. Instead of the NZ() function, use the Transact-SQL statement, COALESCE. The COALESCE statement will return the first non-NULL expression from a list of expressions.

The syntax of the COALESCE statement is as follows:
COALESCE(expression 1, expression 2, ..., expression-n)
where each expression will evaluate to either NULL or a value.

The following T-SQL statement will return the same output as that listed above. 

SELECT Column2, COUNT(COALESCE([Column2], 0)) As Expr1
FROM Table1
GROUP BY Column2
In this case, the COALESCE statement will return the value of Column2 if it is not NULL. If Column2 is NULL, it will return a zero, which will be used by the COUNT statement.


MORE INFORMATION

Steps to Reproduce Behavior

  1. Create the table outlined in step 1 of the "Resolution" section.


  2. Create the following query based on the Table1 table:


    3. 
   Query: Query1
   -------------
   Type: Select Query

   Field: Column2
       Table: Table1
       Total: GroupBy
   Field: Column2
       Table: Table1
       Total: Count
  3. On the Query menu, click Run.

    Note that the result of the query is as follows:


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


REFERENCES

For more information about aggregate functions including the Count function, click Microsoft Access Help on the Help menu, type "perform calculations in a query" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about the COALESCE statement, search for "coalesce" in the SQL Server 7.0 Books Online.

The SQL Server 7.0 Books Online are available from the Microsoft Web site at:

http://support.microsoft.com/download/support/mslfiles/sqlbol.exe

Additional query words: prb 


Keywords          : kbusage kbdta QryOthr 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 13, 1999