By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,723 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

Would like to use IRR in a Query using MS Access

P: 4
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.
Jan 3 '08 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,638
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
Jan 6 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.