Extremely basic question for an Acccess 2000 database. Thanks in advance for any help. I pulled the following Function from the archive. I have the same need described in the post, that is to calculate the geometric mean of the contents of a field. Could someone be kind enough to provide guidance on how to use this function inside a query. I got it compiled as a module ok, but am uncertain just how to successfully utilize it--What should my expression look like in the query window. Basically I need to group input data by "site field" and then calculate geometric mean for all values in the "value field" for the site. After getting it to work, I would try to tackle testing for and dealing with zero values (Likely set to value of 1, then roll into geometric mean calculation.

Archived post and code:

Message #1 by J.R. Pearson <okie_jr@g...> on Wed, 26 Feb 2003 11:35:13 -0600


I copied the following formula that I found in the archives to be able
to run Geometric Averages in Access 97 (my job slow about getting us
updates). It is working great except I need to make a modification in
the formula. I need to modify the formula to handle zeros in the
data. Presently, any zero in the data will result in an error.

Thanks,
J.R.


Public Function GMean3(ParamArray intArgs() As Variant) As Double
Dim dblReturn As Double
Dim intN As Integer
Dim i As Integer

    dblReturn = 0
    intN = 0

    ' Multiply the args together
    For i = LBound(intArgs) To UBound(intArgs)
       If IsNumeric(intArgs(i)) Then
          intN = intN + 1
          If i = LBound(intArgs) Then
             dblReturn = CDbl(intArgs(i))
          Else
             dblReturn = dblReturn * CDbl(intArgs(i))
          End If
       Else
          ' what to do with non-numeric args?
       End If
    Next i

    ' Now take the Nth root
    If intN > 0 Then
       dblReturn = dblReturn ^ (1 / intN)
    Else
       dblReturn = 0
    End If

    GMean3 = dblReturn
End Function

Message #2 by "Charlie Goodwin" <cgoodwin@c...> on Wed, 26 Feb 2003 14:13:57 -0500



Hi,

Howzabout??? Maybe this is too simple?

Private sub TestForZeroInGMean3()
'kill it if there's a zero in the way
If [YourTroublesomeDataNameHere] =0 then ' test
MsgBox"your data missing messager" ' inform if you want
Exit Sub ' get out
Else ' if it's cool run your function
GMean3
End if
End sub

Repeat similar for all data at issue?

Charlie



khwynn