ACC2000: Count Function Ignores Null Values

ID: Q201982

The information in this article applies to:

Microsoft Access 2000

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:

Create Table1 with two text columns as follows:


   Column1    Column2
   -------    -------
   apples
   oranges
   cherries   junk
   null
   notnull    junk

Create the following query based on Table1:


   Query: Query1
   -------------
   Type: Select Query

   Field: Column2
       Table: Table1
       Total: GroupBy
   Field: Expr1: NZ([Column2])
       Table: Table1
       Total: Count

On the Query menu, click Run.

Note that the result of the query is as follows:


   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

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

Create the following query based on the Table1 table:


   Query: Query1
   -------------
   Type: Select Query

   Field: Column2
       Table: Table1
       Total: GroupBy
   Field: Column2
       Table: Table1
       Total: Count

On the Query menu, click Run.

Note that the result of the query is as follows:


   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