Product Function in Access???

[Pottsie]

Hi All,

Hopefully someone may be able to help me.

Is there any way to calculate the Product of a range of numbers in Access? It needs to work exactly as the Product function in Excel which multiples each number in the specified range together. For example .95 x .98 x .98 = 0.91238. I need to be able to use a product function as I want to use it in running sum type of query. i.e.


Year Opening Balance Retirement Rate Closing Balance
1 1000 5% (1000 x (1-.05)) = 950
2 950 2% ( 950 x (1-.02)) = 931

The alternative to doing this by using the Product function is:
Year Opening Balance Retention Rate Closing Balance
1 1000 95% (1000 x (.95)) = 950
2 98% (1000 x (.95 x .98) = 931
3 98% (1000 x (.95 x.98 x.98) =912


Any help would be greatly appreciated.

Cheers
Pottsie

 

[ByteMyzer]

You can paste the following code into a new module and save it:

Public Function Product(ParamArray Number()) As Double
    Dim x As Long
    Product = 1
    For x = 0 To UBound(Number)
        Product = Product * Number(x)
    Next x
End Function

You can then call the function as you would in Excel.

 

[Pottsie]

Thanks for the help ByteMyzer,

However I am struggling with this. I managed to get the function to work in excel but not in access. I have pasted the function into a new module within my database and created the following query.

SELECT YearData.YearID, YearData.RetentionRate, YearData.RetentionFactor, (select Product([tblYear]![RetentionFactor])from tblYear where YearID<=YearData.YearID) AS Rate

FROM tblYear AS YearData

tblYear contains a YearId field and the RetentionFactor fields.

When I run the above query I get the message "At most one record can be returned by this subquery". Can you see what I have done wrong.

Cheers
Pottsie

 

[ByteMyzer]

Ahh, you need the Product function to work like an Aggregate function over a column of fields in a recordset. In that case, you will need to modify the function I supplied in my previous post thus:

Public Function Product(YearID As Variant) As Double
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset( _
        "SELECT tblYear.RetentionFactor " _
        & "FROM tblYear WHERE tblYear.YearID<=" & YearID & ";")
    Product = 1
    Do While Not rst.EOF
        Product = Product * rst!RetentionFactor
        rst.MoveNext
    Loop
    rst.Close
End Function

Also, modify your query thus:

SELECT YearData.YearID, YearData.RetentionRate, YearData.RetentionFactor, Product(YearData.YearID) AS Rate
FROM tblYear AS YearData;


See if this works for you.