Calculate the average for five fields in a query 1

[dmillj]

I submitted a similar post about two months ago. I am trying to average five fields in a query which are named [Result 1], [Result 2], [Result 3], [Result 4], and [Result 5]. I received the following code from PHV which worked great for (4) result fields, but now when I try to use the same code for (5) result fields I am getting an overflow message. Can anyone tell me what I need to change in the code if anything?

'A generic function to get the average value of an arbirtrary numbers of numeric values:
Public Function myAvg(ParamArray Args())
Dim i As Long, N As Long, rv
For i = 0 To UBound(Args)
If IsNumeric(Args(i)) Then rv = rv + Args(i): N = N + 1
Next
myAvg = rv / N ' N=0 <=> rv is null
End Function

The error occurs in the row which states:

myAvg = rv / N ' N=0 <=> rv is null

Thanks for your help.

 

[PHV]

Try this:
If N > 0 Then myAvg = rv / N

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dmillj]

PH,

Thanks for the quick response, that seems to do the trick.

dmillj