How do I pass recordset to ParamArray? 
Author Message
 How do I pass recordset to ParamArray?

Hope you can help!!

I'm having trouble getting a ParamArray to accept a query
recordset to pass values to the array...

I am trying to calculate the Future Value of $1.00 after
applying a series of compound interest rates (this can be
done in Excel with FVSHCEDULE formula but there is no
corresponding function in Access).

My Function "CalcFV" works fine when I input rates in
immediate window:

?CalcFV (.05,.1,.08,-.02)
1.05
1.155
1.2474
1.222452

But when I try to pass a recordset from a 1 column query
using the ParamArray declaration in my sub "GetReturns()"
I get a type mismatch error as it tries to evaluate the FV
Calculation.

I probably not passing the array called "Returns" to my
Function CalcFV correctly... any suggestions?   Thanks for
your help.  Here is my coding:

______________________________________________________
Sub GetReturns()

Dim db As Database
Dim rst As Recordset
Dim Returns As Variant
Dim Counter As Integer
Dim FirstReturn As Double

Set db = CurrentDb
Set rst = db.OpenRecordset("qryStyle Returns Growth",
dbOpenSnapshot)
If rst.EOF Then
    rst.Close
    ReturnsCount = 0
    Exit Sub
Else
    rst.MoveLast
    ReturnsCount = rst.RecordCount
    ReturnsRows = ReturnsCount
    rst.MoveFirst
End If

Returns = rst.GetRows(ReturnsRows)
rst.MoveFirst

'this returns correct number of records in query
Rows = UBound(Returns, 2)

FirstReturn = rst![PeriodicReturn]
Call CalcFV(FirstReturn, Returns)
End Sub

Function CalcFV(ByRef FirstReturn, ParamArray OtherReturns
() As Variant)
Dim StartValue, FV As Double
Dim Counter As Integer

StartValue = 1#

'to calculate first FValue of Starting $1.00
FV = (StartValue * FirstReturn) + StartValue
Debug.Print FV
For Counter = 0 To UBound(OtherReturns)

'to calculate subsequent Future Values produces a type
'mismatch using GetReturns sub where I define 'Returns'
'as array from getrows method...

    FV = (FV * OtherReturns(Counter)) + FV
    Debug.Print FV
    Next
End Function

.



Tue, 19 Apr 2005 00:56:23 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Pass ParamArray to User Defined Function

2. HowTo?: Pass an Array into a ParamArray parameter

3. Passing a ParamArray to an ActiveX Object Method

4. Passing an Array to a ParamArray

5. Passing an array as a ParamArray arguement

6. passing array to ParamArray arg?

7. Passing ParamArrays between routines

8. Passing ParamArray to subfunction

9. Passing around ParamArray

10. Pass-by-reference: what am I doing wrong

11. Recordset: What am I doing wrong?

12. find the top 8 values in recordset, done in code

 

 
Powered by phpBB® Forum Software