AVG fails in report with nulls

[Dennis Hartmann]

ACC 97 - Report with multiple grouping levels. Report footers have text
controls with controlsource: "=Avg([field1])". The report's Recordsource was
changed so field1 contains null (instead of zero) for no value, because the
avearages were misleading. After that change, the report fails with the
following error:

"The Microsoft Jet database engine could not execute the SQL statement
because it contains a field that has an invalid data type."

Remove Avg and report executes, same error if Avg replaced with Sum.

I tried getting around it using:
=Sum(IIf(IsNull ([field1]),0,[field1]))/txtField1Count, where
txtField1Count. Controlsource = "Count([field1])" and other variations of
dividing text controls with appropriat Count and Sum. I also tried
explicitly coercing field1 to numeric value in source query. No Joy.

The KB (210458) indicates the Avg() function should handle nulls. What am I
missing?

..

[Larry Linson]

I don't have Access 97 easily available here, but used the Report Wizard to
create a Report in Access 2003, and it did not exhibit the phenomenon you
describe.

Knowledge Base Article 109364 applies to Access 97 and previous, but it says
essentially the same thing as the article you cite (which applies to Access
2000) that AVG ignores Nulls. But you can't assume that a KB article is
"retroactiv e" and applies to previous versions unless it so states.

You might check that you have both Service Releases and all updates applied
to your installation of Access 97. Other than that, I can only speculate
that something "has gone wrong" with a DLL that supports these functions
(and I don't know which that would be). I am reluctant to suggest that you
uninstall and reinstall Access 97, and the Service Releases, and updates, as
it only _might_ help with the problem. But, it may be the only thing that
would help.

Perhaps, if you have multiple versions of Access installed in the same
directory/folder, that could be a problem, but I have no specific
information on its applicability to _this_ problem.

Larry Linson
Microsoft Access MVP


"Dennis Hartmann" <dh*******@mlco st.comwrote in message
news:13******** *****@corp.supe rnews.com...

ACC 97 - Report with multiple grouping levels. Report footers have text
controls with controlsource: "=Avg([field1])". The report's Recordsource
was
changed so field1 contains null (instead of zero) for no value, because
the
avearages were misleading. After that change, the report fails with the
following error:

"The Microsoft Jet database engine could not execute the SQL statement
because it contains a field that has an invalid data type."

Remove Avg and report executes, same error if Avg replaced with Sum.

I tried getting around it using:
=Sum(IIf(IsNull ([field1]),0,[field1]))/txtField1Count, where
txtField1Count. Controlsource = "Count([field1])" and other variations of
dividing text controls with appropriat Count and Sum. I also tried
explicitly coercing field1 to numeric value in source query. No Joy.

The KB (210458) indicates the Avg() function should handle nulls. What am
I
missing?

.