ACC2000: Count Function Ignores Null Values (201982)



The information in this article applies to:

  • Microsoft Access 2000

This article was previously published under Q201982
Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to 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:
       Column1    Column2
       -------    -------
       apples
       oranges
       cherries   junk
       null
       notnull    junk
    					
  2. 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
    					
  3. 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], <'text'>)) 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 <text>, 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:
       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:
       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 following Microsoft Web site: For SQL Server 2000 Books Online, visit the following Microsoft Web site:

Modification Type:MinorLast Reviewed:8/9/2004
Keywords:kbdownload kbdta kbprb kbusage KB201982