Microsoft KB Archive/201982

= ACC2000: Count Function Ignores Null Values =

Article ID: 201982

Article Last Modified on 11/14/2007

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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:   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], <'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, which will be used by the COUNT statement.



Steps to Reproduce Behavior
 Create the table outlined in step 1 of the "Resolution" section.</li>  Create the following query based on the Table1 table: <pre class="fixed_text">  Query: Query1 -  Type: Select Query

Field: Column2 Table: Table1 Total: GroupBy Field: Column2 Table: Table1 Total: Count </li>  On the Query menu, click Run.

Note that the result of the query is as follows: <pre class="fixed_text">  Column2    Count(Column2) ---   --                    0    junk            2 </li></ol>

<div class="references_section">