Solved: Product Calculation

[mandanarchi]

I know there isn't a native function for calculating the product in Access like there is in Excel, but I'm trying to incorporate our various spreadsheets into our database and therefore I need this capability.

I found this function:

Function Product(strField As String, strTable As String, _
Optional strCriteria As String) As Double
Dim rs As DAO.Recordset
Dim strSql As String
Dim dblResult As Double

'Build the SQL string.
strSql = "SELECT " & strField & " FROM " & strTable & _
" WHERE (" & strField & " Is Not Null) "
If strCriteria <> vbNullString Then
strSql = strSql & " AND (" & strCriteria & ")"
End If

'Loop through the recordset.
Set rs = DBEngine(0)(0).OpenRecordset(strSql & ";")
If rs.RecordCount > 0 Then
dblResult = rs(strField)
rs.MoveNext
Do While Not rs.EOF
dblResult = dblResult * rs(strField)
rs.MoveNext
Loop
End If
rs.Close

Product = dblResult
End Function

I saved it, compiled and tried to run it with

=Product("increase","ppincreases")

and got:
"The object doesn't contain the automation object 'product'"

Can someone spot an obvious mistake, or point me in the direction for another alternative? I've found very few and none seem to work.

Thanks
Mandi

[NeoPa]

Thanks for updating Mandi.

It's probably better to post the solution though. Many will miss the edit note you left indicating that your problem was related to naming the procedure the same as the module it was created in (In this case both named [Product]).