Need help with myAvg module error 6?

[capdownlondon]

I used this module code to average a number of fields based on a childs points score in a day. This works really well -
but if a child is absent for a day and all the values are null then it returns an error 6 message of 'overflow'.
Anyhow here is the module used in the query:

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


and then i put this in the query field box:

Expr1: myAvg([Result 1], [Result 2], [Result 3], [Result 4])

I would really appreciate the help as this is causing huge problems.

Thanks

 

[dhookom]

IMHO this all begins with an un-normalized table structure which requires you to aggregate across field rather than across records.

However, you might want to try change your function to

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
      End If
   Next
   If N = 0 Then
      myAvg = 0 'or null
    Else
      myAvg = rv / N    ' N=0 <=> rv is null
   End If
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]