Multiplying records with certain dates

[antioch]

I've looked through many threads, but didn't see an answer to this problem that I'm trying to solve. I have a query table which has the following 3 fields:

date return compound
3/31/01 A E


6/30/01 B F
9/30/01 C G
12/31/01 D H

I'm trying to find the code to solve for the compound field...so H = (1+D)*(1+C)*(1+B) and G=(1+C)*(1+B)*(1+A), etc. Could anyone help me with this? I really appreciate it!!

[June7]

You need to pull values from multiple records? This won't be easy. How do you know which records go together?

[andy49]

This works I think

Function concmultiple(val As Integer) As Double

Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsQuery As DAO.Recordset
Dim multcount As Integer
Dim totvalue As Double
Set dbs = CurrentDb
totvalue = 1
CurrentDb.QueryDefs("qrymyquery").SQL = "SELECT TOP " & val & " tbl1.percentage, tbl1.ID FROM tbl1;"
'Open a dynaset-type Recordset using a saved query
Set rsQuery = dbs.OpenRecordset("qryMyQuery", dbOpenDynaset)
rsQuery.MoveLast
multcount = rsQuery.Fields("ID")

'MsgBox multcount
rsQuery.MoveFirst
With rsQuery
      Do While Not rsQuery.EOF
      totvalue = totvalue * (.Fields("percentage") + 1)

.MoveNext
Loop
End With
rsQuery.Close
concmultiple = totvalue
End Function

The code could go in any module
And is called from queryrun

you may still have to get round that the ID numbers are necessary

hope it helps anyway

[antioch]

Thanks...I'll try that in the office tomorrow.