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]

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

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

Thank yo

 

[Klatuu]

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]

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

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

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

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

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