WineSnob
Not Bright but TENACIOUS
- Local time
- Today, 12:57
- Joined
- Aug 9, 2010
- Messages
- 211
I am trying to create a table with each year values. I am not geting the 1st year calculation correctly. I think It has to do with where the End If is or I am setting the nextStarting value wrong. Using the test code at the end of this post - I expect year 1 to be 309783.86 and year 2 to be 328370.88
Instead it starts with 32837.88 and increments from there.
Here is the Code:
Sub FillAnnuity(nProposalID As Long, nAnnuityYearsLife As Integer, cInitial As Currency, nRORGB As Double)
Dim rst As Recordset
Dim qdf As QueryDef
Dim GB As Currency
Set qdf = CurrentDb.CreateQueryDef("", "DELETE * FROM [tblAnnuityIncome] WHERE [ProposalID] = " & nProposalID)
qdf.Execute
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAnnuityIncome")
nextStarting = cInitial * (1 + nRORGB) ' calulate the starting value
For nYear = 1 To nAnnuityYearsLife
Debug.Print "nYear: " & nYear & " NextGB: " & nextStarting
If nYear = 1 Then
GB = nextStarting
End If
GB = nextStarting * (1 + nRORGB)
nextStarting = nextStarting * (1 + nRORGB)
Next nYear
End Sub
Public Function testFillannuityTable()
FillAnnuity 1003, 3, 292248.92, 0.06
End Function
Instead it starts with 32837.88 and increments from there.
Here is the Code:
Sub FillAnnuity(nProposalID As Long, nAnnuityYearsLife As Integer, cInitial As Currency, nRORGB As Double)
Dim rst As Recordset
Dim qdf As QueryDef
Dim GB As Currency
Set qdf = CurrentDb.CreateQueryDef("", "DELETE * FROM [tblAnnuityIncome] WHERE [ProposalID] = " & nProposalID)
qdf.Execute
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAnnuityIncome")
nextStarting = cInitial * (1 + nRORGB) ' calulate the starting value
For nYear = 1 To nAnnuityYearsLife
Debug.Print "nYear: " & nYear & " NextGB: " & nextStarting
If nYear = 1 Then
GB = nextStarting
End If
GB = nextStarting * (1 + nRORGB)
nextStarting = nextStarting * (1 + nRORGB)
Next nYear
End Sub
Public Function testFillannuityTable()
FillAnnuity 1003, 3, 292248.92, 0.06
End Function