function with invalid use of null
Posted on 4/19/2005 at 01:35:24 PM by umassbj
any ideas why i'm getting an invalid use of null error....see below...index array is getting null...
Public Function Calc3YearReturn()
Dim dbs As Database
Dim rec As Recordset
Dim fundrec As Recordset
Dim strSql As String
Dim fundarray(1200) As Double
Dim indexarray(1200) As Double
Dim tbillarray(1200) As Double
Dim intarr As Integer
Dim posRet, I1posRet, negRet, I1negRet As Double
Dim k As Integer
Dim OneThousandIn, I1OneThousandIn, TbillOneThousandIn, tbilcumulative, tbilgeometricmonthlyreturn, tbilcompoundedannualreturn As Double
Dim dtBaseDate5Yr As Date
Set dbs = CurrentDb
Dim dbs2 As Database
Set dbs2 = CurrentDb
' Get all possible funds from tblHPIH
' and then loop through calculating stats. for each fund
strSql = "select distinct(hold_code) from tbl_staging"
Set rec = dbs.OpenRecordset(strSql, dbOpenForwardOnly)
If rec.EOF Then
Exit Function ' Nothing to do
End If
Do Until rec.EOF
strSql = "SELECT DISTINCT Date, Return, Index_Return FROM tbl_staging " _
& "WHERE hold_code = '" & rec(0) & "' and date in " _
& " (select max(date) from tbl_staging where hold_code = '" & rec(0) & "'" _
& " GROUP BY year(date), month(date)) ORDER BY date"
Set fundrec = dbs.OpenRecordset(strSql, dbOpenSnapshot)
'initialize arrays
intarr = 1000
OneThousandIn = 1000
I1OneThousandIn = 1000
dtBaseDate5Yr = 0
TbillOneThousandIn = 1000
strSql = "update tbl_staging set OneThousandIn = " & OneThousandIn _
& ", I1OneThousandIn = " & I1OneThousandIn _
& " where date = #" & fundrec(0) & "#" _
& " and hold_code = '" & rec(0) & "'"
dbs2.Execute strSql, dbFailOnError
'initialize arrays
intarr = 1000
fundrec.MoveNext 'move one over as the first record is 1000, 0 , 0
Do Until fundrec.EOF
'Initilaize fundarray, indexarray and t-bill array
fundarray(intarr) = -9999
indexarray(intarr) = -9999
tbillarray(intarr) = -9999
' If rec(0) = "PYHAI" Then
' Debug.Print "PYHAI"
'End If
fundarray(intarr) = fundrec("Return") / 100
indexarray(intarr) = fundrec("Index_Return") / 100
' tbillarray(intarr) = fundrec("TBilPctMo") / 100
OneThousandIn = OneThousandIn * (1 + fundarray(intarr))
I1OneThousandIn = I1OneThousandIn * (1 + indexarray(intarr))
'''' TbillOneThousandIn = TbillOneThousandIn * (1 + tbillarray(intarr))
strSql = "update tbl_staging set OneThousandIn = " & OneThousandIn _
& ", I1OneThousandIn = " & I1OneThousandIn _
& " where date = #" & fundrec(0) & "#" _
& " and hold_code = '" & rec(0) & "'"
dbs2.Execute strSql, dbFailOnError
intarr = intarr + 1
fundrec.MoveNext
Loop
rec.MoveNext
Loop 'rec.Eof - main recordset for funds (holdcodes)
End Function
Replies:
- Re(1): function with invalid use of null - By KevinWilson 4/25/2005 at 05:04:15 PM
You must register before you can post on this board. You can register here.
Post a reply:
Click Here to view the "old"messageboard
MAIN | DOWNLOADS | SAMPLE CODE | STEP BY STEP | DISCUSSION BOARD | LINKS | AUTHOR
