For Next Loop ingores first loop

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
 
These are the values I got running your code

nYear: 1 NextGB: 309783.8552
nYear: 2 NextGB: 328370.886512
nYear: 3 NextGB: 348073.13970272
 
Seems to be starting there to me.
 

Attachments

  • AnnuityTest.jpg
    AnnuityTest.jpg
    58.1 KB · Views: 153
The NextGB is for the next year not the current year.
nYear: 1 NextGB: 309783.8552 This is starting value for year 2
nYear: 2 NextGB: 328370.886512 This is starting value for year 3
nYear: 3 NextGB: 348073.13970272 This is starting value for year 4
 
@pbaldy
Seems to be starting there to me.

That's what the Debug.Print says but then he multiplies it again before he gets out of the loop.

@Winesnob

Comments inline;

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")
[COLOR="Red"]^You create a recordset here, but you never do anything with it????[/COLOR]

nextStarting = cInitial * (1 + nRORGB) ' calulate the starting value
[COLOR="red"]^You set the initial value here. Fine.[/COLOR]
[COLOR="red"]at this point nextstarting = 309783.8552[/COLOR]

For nYear = 1 To nAnnuityYearsLife

Debug.Print "nYear: " & nYear & " NextGB: " & nextStarting

If nYear = 1 Then
GB = nextStarting
[COLOR="red"]^Now you set GB equal to nextstarting.[/COLOR]
[COLOR="Red"]Fine I guess but you never really do anything with GB.[/COLOR]
[COLOR="red"]At this point GB = 309783.8552[/COLOR]
End If


GB = nextStarting * (1 + nRORGB)
[COLOR="red"]^Now you multiply GB [I][B]again[/B][/I]. ?????[/COLOR]
[COLOR="red"]At this point GB = 328370.886512[/COLOR]

nextStarting = nextStarting * (1 + nRORGB)
[COLOR="red"]^Now you multiply nextstarting [I][B]again[/B][/I].????[/COLOR]
[COLOR="red"]At this point nextstarting = 328370.886512[/COLOR]
[COLOR="Red"]which will be the value used when the second loop starts[/COLOR]


Next nYear
End Sub



Public Function testFillannuityTable()
FillAnnuity 1003, 3, 292248.92, 0.06
End Function
 
Thanks guys. See attached db. I expect the table to get filled in with Year1 GuaranteedBase with 309783.8552
Year2 GuaranteedBase with 328370.886512
Year3 GuaranteedBase with 348073.13970272
Is it when it gets written to the table?
 

Attachments

I can't open that right now but I question why you want to write these values to a table. Typically these would just be calculated based on underlying raw data that (should be) already stored.

Is it when it gets written to the table?

There is nothing in the code you posted that writes any values to a table. The problem (as I see it) is that you're multiplying the value too many times.
 
I removed the writing to the table part of the code when I first posted to simplify. It is in the db attached though. I need to write to a table because I need to store them and compare them to Actual values later.
 
Thanks Paul and Sean. I got it to work by adding

If nYear > 1 Then

GB = nextStarting * (1 + nRORGB)

nextStarting = nextStarting * (1 + nRORGB)

End If
 

Users who are viewing this thread

Back
Top Bottom