Re: PRODUCT calculation in queries

Tech-Archive recommends: Speed Up your PC by fixing your registry

What error are you getting?
Which line generates the error?

After pasting it into the code window, choose Compile on the Debug menu.
Does it compile okay?

If you are using Access 2000 or 2002, you may need to add a reference to the
Microsoft DAO Library. More on references:
http://allenbrowne.com/ser-38.html

Once you have it running, you can use it just like one of the built-in
functions in any query.

You cannot reverse-engineer the built-in functions.

--
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" <JoeMiller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:056F9137-00BA-45EC-AB07-40CA8ECD8268@xxxxxxxxxxxxxxxx

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

"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

  • Re: PRODUCT calculation in queries
    ... Tips for Access users - http://allenbrowne.com/tips.html ... functions in any query. ... Function Product(strField As String, strTable As String, _ ... Dim dblResult As Double ...
    (microsoft.public.access.queries)
  • Re: PRODUCT calculation in queries
    ... The Microsoft Jet database engine could not find the input table or query ... After pasting it into the code window, choose Compile on the Debug menu. ... Function Product(strField As String, strTable As String, _ ... Dim dblResult As Double ...
    (microsoft.public.access.queries)
  • Re: PRODUCT calculation in queries
    ... together with my relative ineptitude with VBA and with maths in general. ... Function Product(strField As String, strTable As String, _ ... Dim rs As DAO.Recordset ... Dim dblResult As Double ...
    (microsoft.public.access.queries)
  • Re: PRODUCT calculation in queries
    ... into a standard module. ... Function Product(strField As String, strTable As String, _ ... Dim rs As DAO.Recordset ... Dim dblResult As Double ...
    (microsoft.public.access.queries)
  • Re: Extract numbers from a string
    ... >> make table query but don't know how to call a module ... >> from a string that I can adapt for my sitation, ... >In the Database Window, ... >All you should have in the code window now is ...
    (microsoft.public.access.queries)