Would like to use IRR in a Query using MS Access

[BertDick]

I'm trying to use the IRR function in Ms Access using the code below.

Function MyIRR(mygroupid, myguess) as double
Dim Values(4) as double' an array with 5 elements
Dim x

dim rs as dao.recordset
set rs = currentdb.openrecordset("Select * from Myquery where Fundid =" & mygroupid)
rs.movefirst ' should only be one record

for x = 0 to 4
Values(x) = rs(x+1)
next x

MyIRR = IRR(Values(),myguess)

rs.close
set rs = nothing

End Function

In this procedure I having a query(MYQuery) to group on the first column which is FundiD.


I Created a second query (Myquery1)using the group query as source and included all fields plus an extra column to calculate My IRR.
The extra column in the second query(Myquery1) looks like this :IRRVal:MyIRR(FundID, .10)

1st Qry fiedls are: FundiD, CapCallAmt,Dictributions, Net asset Value and Cashflow. ( cashflow is the sum of the fields in the row) The problem is, when i run the query error # 3061 Run Time error and To few parameters Expected 1 . Can anyone help.

I'm stuck for the last 3 days. Now learing to use Visual Basic.

[ADezii]

I'm trying to use the IRR function in Ms Access using the code below.

Function MyIRR(mygroupid, myguess) as double
Dim Values(4) as double' an array with 5 elements
Dim x

dim rs as dao.recordset
set rs = currentdb.openrecordset("Select * from Myquery where Fundid =" & mygroupid)
rs.movefirst ' should only be one record

for x = 0 to 4
Values(x) = rs(x+1)
next x

MyIRR = IRR(Values(),myguess)

rs.close
set rs = nothing

End Function

In this procedure I having a query(MYQuery) to group on the first column which is FundiD.


I Created a second query (Myquery1)using the group query as source and included all fields plus an extra column to calculate My IRR.
The extra column in the second query(Myquery1) looks like this :IRRVal:MyIRR(FundID, .10)

1st Qry fiedls are: FundiD, CapCallAmt,Dictributions, Net asset Value and Cashflow. ( cashflow is the sum of the fields in the row) The problem is, when i run the query error # 3061 Run Time error and To few parameters Expected 1 . Can anyone help.

I'm stuck for the last 3 days. Now learing to use Visual Basic.

You may want to take a look at this Post:
How to Calculate the IRR in Access