Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: Count Function Ignores Null Values

ACC2000: Count Function Ignores Null Values

View products that this article applies to.

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).

↑ Back to the top

Symptoms

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

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

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:
http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/SQLBOL.exe
For SQL Server 2000 Books Online, visit the following Microsoft Web site:
http://technet.microsoft.com/en-us/sqlserver/bb331756.aspx

↑ Back to the top

Keywords: KB201982, kbusage, kbprb, kbdta, kbdownload

↑ Back to the top

Article Info
Article ID : 201982
Revision : 5
Created on : 11/14/2007
Published on : 11/14/2007
Exists online : False
Views : 401