Product Function in Access

[mwca]

Is it possible to use the PRODUCT function from Excel in a totals query in Access?

If I need to write a new function, how do I do this - I know how to write a function, but not if there are a variable number of parameters!

 

[allanon]

To write a function with a variable number of parameters just make them optional. ie:


Public Function DoesSomething(Param1 as String, Optional Param2 as String = "Default", Optional Param3 as Integer = 100) as Sring
DoesSomething = Param1 & " " & Param2 & " " & Param3
end Function


Calling this in a routine would give you:

A) myField = DoesSomething("My String", "Another String", 50)

myField would = My String Another String 50

b) myField = DoesSomething("My String", "Another String&quot

myField would = My String Another String 100

c) myField = DoesSomething("My String&quot

myField would = My String Default 100

Make Sense? The optional ones must be the last parameters and you can supply them with a default value if they are not entered. All parameter rules apply.

 

[MichaelRed]

Hmmmmmmmmmmmmmmmm,

A rather klutzy approach, but perhaps the question 'merits' it?

Introduction of parameter arrays would, perhaps, be more than you think mwca can absorb in a twelve step program?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[allanon]

I Agree that this is much simpler than a parameter array. I have used them a couple of times but I still run into problems once in a while. This approach is easy and quick.

 

[mwca]

thanks for the answers, but not sure I fully understand!
essentially all I need is a function that will say

Product()= x1*x2*x3*...*xn

in much the same way that

Sum()= x1+x2+x3+...+xn

could you let me know how i would do this???

 

[allanon]

What is x1, x2, x3? Are these to be values passed to the function. If so then you will need to have a parameter array.

The easiest way for that is this:

Place the following code in a module.

Public Function Product(ParamArray MyValues() As Variant) As Double
Dim i As Integer
Product= 0
For i = 0 To UBound(MyValues)
If IsNumeric(MyValue(i)) Then
Product= Product * CDbl(MyValues(i))
End If
Next i
End Function

Then you would call this function like this:

Product(x1, x2, x3, ..., xn)

This can be called in code or in a query.

Code:

MyProduct = Product(x1, x2, x3, ..., xn)

Query:

SELECT Product(table1.field1, table1.field2, table1.field3, ..., table1.fieldn) as MyProduct FROM table1


Have Fun

 

[mwca]

Thank you very much!

 

[mwca]

Sorry - 1 more question! The above works great (though I had to put Product=1 in the code instead) but how can I make it an aggregate function, so I can use it in a totals query?
i.e I want to be able to say Product(table1.field1) and have it multiply together all the values in field1.

any ideas?

 

[allanon]

Oh..ok now I understand. There is no function to do that. If you want to do that you will have to do it with code.

Here is code that will do that:

Public Function DoProduct() As Double
Dim rs As DAO.Recordset
Dim myProduct As Double

myProduct = 1
Set rs = CurrentDb.OpenRecordset("Table2", dbOpenSnapshot)
While Not (rs.EOF)
myProduct = myProduct * rs!myField
rs.MoveNext
Wend
DoProduct = myProduct
End Function

To call it use: txtOutput = DoProduct()

One issue to consider....if any record is zero the entire result will be zero (anything * zero = zero)

 

[mwca]

Is it possible to call this in a query?

I don't know enough about how to use code to make it run...

When I try to use it in a query, an error message comes up saying "Undefined function 'DoProduct' in expression"

 

[allanon]

You will not be able to call this in a query. Tell me how you want to use it and I can help you set it up. The problem with using this in a query is that a query reads and processes on row at a time. This function will not help you with that.

I imagine that you want the results of this function to go into a text box. This is not a problem. Tell me that name of your table and the field you are multiplying. I will modify the query for you. Then in the control source for the text field just type =DoProduct() then you are done.

 

[MichaelRed]

without going into details, there is no reason the procedure could not be instantiated within a query.

On the other hand, it would be a somewhat foolish exercise, as it would return the same value for each record, and thus simply use computer resources to regenerate the value for each record in the cartesian product of the query source(s).

Finally, this appears to have gone full (or just half?) circle. The original post alluded to the assignment of the results of hte function to a simple variable, suggesting the use in a form or report, and hinting at a 'rowset' aggregate. Only in the latter posts, does the concept of using the function in a columset as part of a query arise.

I'm sure that I wish i hadn't intejected any commentary into this thread (then I wouldn't continue to get the e-mail notices), but i'm also beginning to think the effort of attempting to help here is -at best- a total waste of time (and the -at worst- case is beyond the 'pale ...' and mwca is either incapable of knowing what they want (or how to express it) or just 'fishing' to see who they can get to continue to develop on their behalf - regardless of the 'reality' of the request). MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[allanon]

MichaelRed, thanks for the comments and you are of course correct when you said that it can be called from a query. I said it can't simple because to do so would be silly. I still find it difficult to believe that anyone would have use or need to have any sort of a PRODUCT function. The concept of one leads me to thinking of all the reasons why a real one would not work. Even on this one that I wrote up I ran it against a small table, 23 rows, and the answer it gave was something like 2.153957e43. I don't know about you but I rarely use numbers that large in the work I do.

Once again, thanks for the comments.

Jerry

 

[mwca]

I'm sorry for all the confusion and hassle I have caused...
Basically I have a number of items and for each item I work out a return (%) for each item for each month. If I want to work out the return on each item for a whole year (or any other period), this is calculated by the formula:

(1+R) = (1+r1)*(1+r2)*(1+r3)*...*(1+rn)

where R is the return for the year and r1 is the return for month1 etc.
So I have a query that gives me something like:
ID Date 1+return
101 31/1/02 1.052
101 28/2/02 1.036
101 31/3/02 1.027
102 31/1/02 1.043
102 28/2/02 1.032
102 31/3/02 1.061
etc.
but there are hundreds of ID's and a return for each month for each ID
To work out the return for 101 for the first 3 months of this year would be 1.052*1.036*1.027 = 1.119, so the return is 11.9%
I thought that I would be able to do this by using a totals query, grouping by ID and instead of using the SUM function, use PRODUCT (until I realised Access doesn't have it!)
I can do this by exporting to Excel (which does have a product function option when doing subtotals) but I would much rather be able to do it all in Access

I appreciate the amount of time you have spent on this - I didn't realise it would be so complicated!