Add function to a query

[Radhika]

How can I add a function to an expression in query? I have the following
expression:
OPSE Avg All: (Nz([OPSE cracker Avg],0)+Nz([OPSE Pudding Avg],0)+Nz([OPSE 10
ml Avg],0))/CountItems([OPSE cracker Avg],[OPSE Pudding Avg],[OPSE 10 ml Avg])

I created a module called CountItems as follows:
Public Function CountItems(ParamArray varItems()) As Long
'Purpose: Count the number of non-null items passed in.
'Example: CountItems([OPSE cracker Avg Avg], [OPSE Pudding Avg],
[OPSE 10 ml Avg])
Dim i As Integer
Dim lngCount As Long
For i = LBound(varItems) To UBound(varItems)
If Not IsNull(varItems(i)) Then
lngCount = lngCount + 1
End If
Next
CountItems = lngCount
End Function

How do I incorporate this into the expression. If I try to run the query, it
gives me a msg saying "Undefined function CountItems in expression"

How can I solve this?

Thankyou,
Radhika

 

[Duane Hookom]

Just a quick note: Your module name can't be the same as the function name.
Find and use a naming convention that prevents issues like this.

 

[John Spencer]

Try pasting the following function into a module and saving the module with a
name other than fRowAverage. You would call it as

Field: OPSE Avg All: fRowAverage([OPSE cracker Avg],
[OPSE Pudding Avg],[OPSE 10 ml Avg])

'========================= Code Starts ===================================
Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call:
'myAvg = fRowAverage("1","TEST","2", "3",4,5,6,0) returns 3 '(21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then 'At least 1 number in the group of values
fRowAverage = dblSum / intElementCount
Else
fRowAverage = Null 'No number in the group of values
End If

End Function
'========================= Code Ends ===================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County