ACC2000: Count Function Ignores Null Values (201982)

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