Access 97 Report Problem......

[Steve Hirsch]

Hi All;

I have a plant running reports on Access 97. I know it's
old but this is the way they want/can afford it.

Here was my assignment:

They asked me to (as required by the government) to
change their reports from calculating Arithmetic Mean
(Average) to Geometric mean. The data in the table
contains numbers and Nulls. Access does not have a
=GEOMEAN() function like excel so it has to be done like
this:

Take the base 10 Logarithm of each number in the series.
Take the average =Avg() of those base 10 logs.
Take the base 10 "anitlog" =10^() of the average and
voila, you have the geometric mean of your queried data.

I had to use a VB function to calculate the Base 10
Logarithm with the following code:

Static Function Log10(X)
Log10 = Log(X) / Log(10#)
End Function

My query criteria field looked like:

Expr1: IIf(

![Field]>0 ,Log10(

![Field]))

The query works fine using this VB Function and
criteria. In my report field I had the following:

=10^(Avg([Query]![Field]))

When I tested this using Access 2002 it worked fine.
When I put this code into the same database in Access 97,
the query ran fine, but the report generated the
following error:

"The Microsoft Jet Database Engine could not execute the
SQL Statement because it contained a field that has an
invalid data type."

I tried the following:

1. Forcing the Query field to a numeric in the query
definition.
2. Modifying the VB code to convert every value in the
query to a numeric. (Using - x=int(x))
3. Modifying the report to =10^(AVG(Int([Query]!
[Field]))).
4. Installing the MSJet DB patch7.

Any suggesstions as to why this is working in 2002 and
not Access 97?

TIA;
Steve

 

[Jeff Boyce]

Steve

The error message suggests (yah, me too! I always have to guess and
interpret to figure out what the error message REALLY is telling me) that
data in your expression doesn't match other data in your expression.

I noticed that you are not handling nulls with something like an Nz()
function (null-to-zero). What requirement do you have for
including/excluding null values in calculating your mean?

Good luck

Jeff Boyce
<Access MVP>

 

[Steve Hirsch]

Hi Jeff;

Thanks for the response.

I have the iif(.... in my query criteria. It passes the nulls to the query
results and passes the >0 through the LOG10 function I defined in VB.

I still am having the same problem. Do I need to pass the nulls through an
NZ() function?

Steve

 

[Jeff Boyce]

Steve

You and I may have a different interpretation of "null". Your IIF()
expression only has a "true" value, doesn't evaluate for "Null", and doesn't
indicate what to do if the amount is not >0.

In Access, "Null" means nothing there, not a space, not a "" (zero-length
string), nothing, nada.

Another way to write your expression (and this is what I was suggesting
before):

Expr1: IIf(Nz(

![Field],0)>0 ,Log10(

![Field]),0)

Please notice that I've assumed you'll use a zero if the value in the field
is 0 or null. I don't know what that will do to your subsequent calculation
(see earlier response).

Another approach would be to select ONLY those rows with non-null (?and
non-zero) values for the field to do your calculation.

Good luck

Jeff Boyce
<Access MVP>