Re: PRODUCT calculation in queries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi,

Thanks for the reponse, I've given it a whirl, but it throws up a runtime
error. I'm sure the problem lies with the limitations of my instructions,
together with my relative ineptitude with VBA and with maths in general.

What I really need is a basic generic Product function that can simply be
dropped into any query. After that I can simply use queries to fiddle with
the critera aspects of things. I would have assumed that a function based on
similar code/maths principles as the existing series-based functions (Count,
Sum etc) shouldn't be that difficult to write.

Is there any way to see the underlying code behind the standard functions
like Sum - if you could take this particular function and modify the code by
changing the addition operator to a multiplication operator that would do the
trick.

All of these assumptions are made by someone with a very basic maths
education so feel free to highlight my naivety!


Joe

"Allen Browne" wrote:

This is untested aircode, but you should be able to save the function below
into a standard module (Modules tab of Database window). Then in a query,
type an expression like this into a fresh column in the Field row:
Product("Table1", "Field3", "ID <> " & [ID])

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Joe Miller" <Joe Miller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ABE55634-37CC-4962-AE76-9B918A94B67E@xxxxxxxxxxxxxxxx
Hi,

I'm having trouble creating a query/ expression that will multiply all of
the values in a field together (i.e return the Product of those numbers.)

I have a table with several records, Field3 of which contains numbers
(with
a few decimal places.) Ultimately, I want to be able to calculate, for
each
record in the table/query, the product of all of the other Field3 values
excluding its own (i.e the expression will return for Record #1 the
product
of Field3 in records # 2-10, the expression for record #2 will return the
product of records#1,3-10 etc)

At the moment, however, I'm simply struggling to be able to return any
sort
of product at all. I don't seem to be able to locate any equivalent of the
Excel PRODUCT function.

Cany anyone with better maths skills than me help!?
Joe




.

Relevant Pages