Average of multiple fields

[glmtd]

Hello everyone. I am trying to calculate an average of multiple fields (Field1, Field 2, Field 3) in a query in Access 2010. Can someone help me to create a module that would calculate the average of multiple fields with 0 as valid value, but ignore the blanks. So if Field1 is blank, Field2 is 1, and Field3 is 2, their average would turn out to be 1.5, as blank would be ignored. Consequently, if Field1 is 0, Field2 is 1, and Field3 is 2, their average would be 1 ( (0+1+3)/3).

Here is a script that I found which deals with 0, but would like for it to utilize 0 and ignore blanks.

Thanks

Script:
Public Function Average_Of_Fields(ParamArray flds() As Variant) As Double
Dim i As Byte, sumFields As Long, numFields As Byte

For i = LBound(flds) To UBound(flds)
If Nz(flds(i), 0) <> 0 Then


sumFields = sumFields + flds(i)
numFields = numFields + 1
End If
Next

If numFields <> 0 Then
Average_Of_Fields = sumFields / numFields
End If
End Function

[pbaldy]

Try

If Len(flds(i) & vbNullString) > 0 Then

[glmtd]

Thank You! That did the job.

[pbaldy]

No problem, and welcome to the site by the way!