Count Function Ignores Null Values

[Paul]

Does Microsoft Knowledge Base Article - 201982 RE: Access
2000 Count Function Ignores Null Values apply to Acess
2002? I can duplicate the problem the Article refers to
but is work-around does not work. The SQL solution
returns a "Syntax error (missing operator)"in querry
expression and the database view a "Syntax error in querry
expression" error. See below ...




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


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 <text>, which will be used by the COUNT statement.
MORE INFORMATION

 

[Gary Walter]

Hi Paul,

Is there a chance you have a "References"
problem?

http://support.microsoft.com/support/kb/articles/Q208/2/18.ASP

 

[Michel Walsh]

Hi,


COUNT(*) counts the null, COUNT(fieldname) does not count the null, that is
accordingly to the SQL standard behavior, and being part of the standard,
yes, I still work the way it should... I fail to see why the author of the
article has to resort to Nz or to COALESCE when COUNT(*) would have done the
job very easily, on the other hand... maybe just in a spirit to introduce
those useful functions to the intended audience?


Hoping it may help,
Vanderghast, Access MVP

Does Microsoft Knowledge Base Article - 201982 RE: Access
2000 Count Function Ignores Null Values apply to Acess
2002? I can duplicate the problem the Article refers to
but is work-around does not work. The SQL solution
returns a "Syntax error (missing operator)"in querry
expression and the database view a "Syntax error in querry
expression" error. See below ...




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


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 <text>, which will be used by the COUNT statement.
MORE INFORMATION

 

[Lucifuge752]

This is not resolved in Microsoft Query

I am trying to use a Query in Microsoft Excel to get information from our Maximo database and some of the values NEEDED are the 0 or Null values. Microsoft Query works like an abbreviated version of Access but the Count(*) still ignores the null values even though the problem was evidently addressed in Access.

Does anyone know a workaround in Microsoft Query or the best way to have this type of query built into Excel? I am connecting to an Oracle database and have had no problem getting the exact information I am looking for the way I need it other than these Null values.

Thank you

 

[Guest]

The problem isn't really fixed in Access. The problem that causes the Nulls
is that if a cell is formatted a General and no entry has been made in the
cell, it returns Null. If you format the cell as any kind of number and no
entry has been made, it returns 0. One solution is to be sure the cells with
numbers are formatted as numbers; however, since we can't count on users to
do this consistantly, we can use the Nz function to ensure we get a zero
returned. Put the Nz function around the value returned from the cell, not
the value returned from a calculation.

Bad:

= Nz(NumFld1 * NumFld2, 0)

Good

= Nz(NumFld1, 0) * Nz(NumFld2, 0)

 

[Lucifuge752]

Microsoft Query does not use the NZ command

Unfortunately, Microsoft Query does not use the NZ function so that does not help. Originally, I thought this was a problem with the count command (and it still might be) but I found that even when not using the count command, I cannot display any of the records that have the null values.

For some reason, Microsoft Query AND Microsoft Access will not extract these records from the Maximo Oracle database. Using the Brio (Brio Query, Brio Intelligence, Hyperion Intelligence etc.) program, I get all of the records with the proper count including the null values as a 0. Though I can export the values from Brio into Excel and copy them over etc, I cannot count on other users having Brio so I need it built into the Microsoft Excel Spreadsheet which forces me back to Microsoft Query.

The workaround I have so far is using VLookup to see if the record I need is found in the query results, and if not, then I have a 0 substituted for the NA result.

The problem with this is that VLookup requires the source information to be sorted NUMERICALLY and that is another ball of wax when dealing with a combination of numbers and alphanumeric values. I cannot sort the information in Query since it does the same alphanumeric sort which keeps refreshing in Excel every time I update the information.

Bottom line on the null values is that my queries are not getting those records at all. The count issue is a side problem and VLookup is another side problem.