CODEWITHSUNDEEP
ms-accessvba
beeba
beeba

Reputation: 432

Loop in VBA Skipping Values

I have the following code in Access VBA. 

Public Sub CalculateVol()

Dim vol As Double
Dim rs As Recordset
Dim rs2 As Recordset
Dim iRow As Long, iField As Long
Dim strSQL As String
Dim CurveID As Long
Dim MarkRunID As Long
Dim MaxOfMarkAsofDate As Date
Dim userdate As String

DoCmd.RunSQL "DELETE * FROM HolderTable"
'Clears out the old array from the holder table.

Dim I As Integer
Dim x As Date

userdate = InputBox("Please Enter the Date (mm/dd/yyyy)")

x = userdate

Dim BucketTermAmt As Long
BucketTermAmt = InputBox("Please Enter the Term Amount")

For I = 0 To 76

MaxOfMarkAsofDate = x - I



strSQL = "SELECT * FROM VolatilityOutput WHERE CurveID=" & Forms!Volatility.cboCurve.Value & " AND MaxOfMarkAsofDate=#" & MaxOfMarkAsofDate & "# ORDER BY MaxOfMarkasOfDate, MaturityDate"

Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)
Set rs2 = CurrentDb.OpenRecordset("HolderTable")



If rs.RecordCount <> 0 Then

    rs.MoveFirst

    rs.MoveLast


    Dim BucketTermUnit As String
    Dim BucketDate As Date
    Dim MarkAsOfDate As Date
    Dim InterpRate As Double


    Dim b As String

    b = BucketTermAmt

    BucketTermUnit = Forms!Volatility.cboDate.Value
    BucketDate = DateAdd(BucketTermUnit, b, MaxOfMarkAsofDate)
    InterpRate = CurveInterpolateRecordset(rs, BucketDate)

    rs2.AddNew
    rs2("BucketDate") = BucketDate
    rs2("InterpRate") = InterpRate
    rs2.Update


End If

Next I


vol = EWMA(0.94)

Forms!Volatility!txtVol = vol

Debug.Print vol


End Sub

The basic idea is that the user inputs a date for MaxofMarkAsofDate. The code then finds that instance of MarkAsofDate in the table VolatilityOutput, and uses it as a reference point to calculate InterpRate. It stores this number in the HolderTable. Then it loops the same procedure, except using one day previous to the user-inputted MarkAsofDate, and then one day previous to that, and so on for a total of 76 times.

The first part works fine but the loop is giving me trouble. If it doesn't find the user-inputted date in the table, it'll just skip it, but still count it as a loop. So while I want 76 data points, I might only end up with 56, for example, if it skips 20 dates. So I want to either stop it from skipping, or just keep looping until HolderTable has a total of 76 numbers in it. How do I do this?

Upvotes: 0

Views: 281

Answers (1)

Sobigen
Sobigen

Reputation: 2179

Sounds like you want a while loop since the for loop as written will always go the same number of times. Looks like you might need a second counter to increment your date. 

while count < 76
    'get rs here
    if rs.RecordCount <> 0 Then
        'do everything else
        count = count + 1
    end if
    dateCounter = dateCounter + 1
loop

Upvotes: 2

Related Questions