Loop not looping correct # of times

[WineSnob]

I am trying to get a currency amt for a number of years. The amount will change based on the year. The code only loops thru the first 6 years (which is the (nbucket1duration). Why doesn't it continue until (nProposalYears).
Here is the code with testing code at the bottom.

Function fnDesiredIncome(ClientMonthlyInc As Currency, InflationFactor As Double, nProposalYears As Integer _
, nbucket1duration As Integer, nbucket2duration As Integer, nbucket3duration As Integer _
, nbucket4duration As Integer, nbucket5duration As Integer, nbucket6duration As Integer _
, nbucket7duration As Integer, nbucket8duration As Integer, nbucket9duration As Integer) As Currency
Dim nInflationBucket As Integer
Dim nLastInflationBucket As Integer
Dim iYear As Integer
Dim StartIncomeAmt As Currency
Dim StartBucket1Year As Integer
Dim StartBucket2Year As Integer
Dim StartBucket3Year As Integer
Dim StartBucket4Year As Integer
Dim StartBucket5Year As Integer
Dim StartBucket6Year As Integer
Dim StartBucket7Year As Integer
Dim StartBucket8Year As Integer
Dim StartBucket9Year As Integer
Dim EndBucket1Year As Integer
Dim EndBucket2Year As Integer
Dim EndBucket3Year As Integer
Dim EndBucket4Year As Integer
Dim EndBucket5Year As Integer
Dim EndBucket6Year As Integer
Dim EndBucket7Year As Integer
Dim EndBucket8Year As Integer
Dim EndBucket9Year As Integer
'''Defined Variables
 nID = 1
 StartBucket1Year = 1
 EndBucket1Year = StartBucket1Year + nbucket1duration - 1
 StartBucket2Year = EndBucket1Year + 1
 EndBucket2Year = StartBucket2Year + nbucket2duration - 1
 StartBucket3Year = EndBucket2Year + 1
 EndBucket3Year = StartBucket3Year + nbucket3duration - 1
 StartBucket4Year = EndBucket3Year + 1
 EndBucket4Year = StartBucket4Year + nbucket4duration - 1
 StartBucket5Year = EndBucket4Year + 1
 EndBucket5Year = StartBucket5Year + nbucket5duration - 1
 StartBucket6Year = EndBucket5Year + 1
 EndBucket6Year = StartBucket6Year + nbucket6duration - 1
 StartBucket7Year = EndBucket6Year + 1
 EndBucket7Year = StartBucket7Year + nbucket7duration - 1
 StartBucket8Year = EndBucket7Year + 1
 EndBucket8Year = StartBucket8Year + nbucket8duration - 1
 StartBucket9Year = EndBucket8Year + 1
 EndBucket9Year = StartBucket9Year + nbucket9duration - 1
 
For iYear = StartBucket1Year To nProposalYears
    If iYear <= EndBucket1Year Then
        StartIncomeAmt = ClientMonthlyInc
 
    Debug.Print iYear, StartIncomeAmt
 
        If iYear > EndBucket1Year < EndBucket2Year Then
              StartIncomeAmt = ClientMonthlyInc * (1 + InflationFactor) ^ EndBucket1Year
 
            If iYear > EndBucket2Year < EndBucket3Year Then
              StartIncomeAmt = ClientMonthlyInc * (1 + InflationFactor) ^ EndBucket2Year
 
    End If
        End If
            End If
 
Next iYear
 
fnDesiredIncome = StartIncomeAmt
End Function

Function RunTest()
    fnDesiredIncome 5000, 0.03, 11, 6, 2, 5, 6, 9, 0, 0, 0, 0
    'ClientMonthlyInc , InflationFactor , nProposalYears , nYear, bucketdurations 1 thru 9
End Function

 

[VilaRestal]

If iYear <= EndBucket1Year Then

It's looping all the way but it's only doing anything for the first EndBucket1Year loops

 

[WineSnob]

Thanks. I am actually writing the values to a table. What is the syntax to select iYears between two years.

If iYear = EndBucket1Year < EndBucket2Year Then

What I want is for income in the table to be
YR 1 = 5000
Yr 2 = 5000
YR3 = 5000
Yr4=5000
YR5=5000
YR6=5000
YR7=5970.26
YR8 =5970.26
YR9 through YR11 =6333.85.


Here is the full code and attached db.

Function fnDesiredIncome(ClientMonthlyInc As Currency, InflationFactor As Double, nProposalYears As Integer _
, nbucket1duration As Integer, nbucket2duration As Integer, nbucket3duration As Integer _
, nbucket4duration As Integer, nbucket5duration As Integer, nbucket6duration As Integer _
, nbucket7duration As Integer, nbucket8duration As Integer, nbucket9duration As Integer _
, nProposalID As Integer) As Currency
Dim rst As Recordset
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("", "DELETE * FROM [tblIncomeTemp] WHERE [ProposalID] = " & nProposalID)
qdf.Execute
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblIncomeTemp")
Dim nInflationBucket As Integer
Dim nLastInflationBucket As Integer
Dim iYear As Integer
Dim StartIncomeAmt As Currency
Dim StartBucket1Year As Integer
Dim StartBucket2Year As Integer
Dim StartBucket3Year As Integer
Dim StartBucket4Year As Integer
Dim StartBucket5Year As Integer
Dim StartBucket6Year As Integer
Dim StartBucket7Year As Integer
Dim StartBucket8Year As Integer
Dim StartBucket9Year As Integer
Dim EndBucket1Year As Integer
Dim EndBucket2Year As Integer
Dim EndBucket3Year As Integer
Dim EndBucket4Year As Integer
Dim EndBucket5Year As Integer
Dim EndBucket6Year As Integer
Dim EndBucket7Year As Integer
Dim EndBucket8Year As Integer
Dim EndBucket9Year As Integer
'''Defined Variables
 nID = 1
 StartBucket1Year = 1
 EndBucket1Year = StartBucket1Year + nbucket1duration - 1
 StartBucket2Year = EndBucket1Year + 1
 EndBucket2Year = StartBucket2Year + nbucket2duration - 1
 StartBucket3Year = EndBucket2Year + 1
 EndBucket3Year = StartBucket3Year + nbucket3duration - 1
 StartBucket4Year = EndBucket3Year + 1
 EndBucket4Year = StartBucket4Year + nbucket4duration - 1
 StartBucket5Year = EndBucket4Year + 1
 EndBucket5Year = StartBucket5Year + nbucket5duration - 1
 StartBucket6Year = EndBucket5Year + 1
 EndBucket6Year = StartBucket6Year + nbucket6duration - 1
 StartBucket7Year = EndBucket6Year + 1
 EndBucket7Year = StartBucket7Year + nbucket7duration - 1
 StartBucket8Year = EndBucket7Year + 1
 EndBucket8Year = StartBucket8Year + nbucket8duration - 1
 StartBucket9Year = EndBucket8Year + 1
 EndBucket9Year = StartBucket9Year + nbucket9duration - 1
 
For iYear = StartBucket1Year To nProposalYears
    If iYear <= EndBucket1Year Then
        StartIncomeAmt = ClientMonthlyInc
        With rst
        .AddNew
        ![ProposalID] = nProposalID
        ![Yr] = iYear
        ![Desired Income] = StartIncomeAmt
         .Update
            End With
     End If
    Debug.Print iYear, StartIncomeAmt
 
        If iYear = EndBucket1Year < EndBucket2Year Then
              StartIncomeAmt = ClientMonthlyInc * (1 + InflationFactor) ^ EndBucket1Year
             With rst
        .AddNew
        ![ProposalID] = nProposalID
        ![Yr] = iYear
        ![Desired Income] = StartIncomeAmt
         .Update
            End With
        End If
 
            If iYear = EndBucket2Year <> EndBucket3Year Then
              StartIncomeAmt = ClientMonthlyInc * (1 + InflationFactor) ^ EndBucket2Year
              With rst
        .AddNew
        ![ProposalID] = nProposalID
        ![Yr] = iYear
        ![Desired Income] = StartIncomeAmt
         .Update
            End With
             End If
 
            With rst
        .AddNew
        ![ProposalID] = nProposalID
        ![Yr] = iYear
        ![Desired Income] = StartIncomeAmt
         .Update
            End With
 
Next iYear
 
fnDesiredIncome = StartIncomeAmt
End Function

Function RunTest()
    fnDesiredIncome 5000, 0.03, 11, 6, 2, 5, 6, 9, 0, 0, 0, 0, 1003
    'ClientMonthlyInc , InflationFactor , nProposalYears , nYear, bucketdurations 1 thru 9
End Function

 

[VilaRestal]

Just use elseif

If iYear < EndBucket1Year Then

ElseIf iYear < EndBucket2Year Then

ElseIf iYear < EndBucket3Year Then

ElseIf iYear < EndBucket4Year Then

etc

Two pieces of advice:

1) Please sort out your code indentation. It would make it a lot easier to read.

2) Have you considered using arrays rather these long lists of variables? I think it would make it a lot easier to write and you might find you can loop through array elements rather than repeat almost the same code over and over. A function can accept an array as a parameter by declaring it as a variant.

 

[WineSnob]

I'll give that a try.