mysql - 在 Access VBA 中循环 Access strSQL 中的日期

Question

我在 Access 中有此代码:

Sub SampleReadCurve()

Dim rs 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



CurveID = 15

MaxOfMarkAsofDate = #7/22/2015#


strSQL = "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & " AND MaxOfMarkAsofDate=#" & MaxOfMarkAsofDate & "# ORDER BY MaxOfMarkasOfDate, MaturityDate"


Debug.Print strSQL

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

If rs.RecordCount <> 0 Then
    rs.MoveFirst
    Debug.Print vbCrLf
    Debug.Print "First", rs.Fields("ZeroCurveID"), rs.Fields("MaturityDate"), rs.Fields("ZeroRate"), rs.Fields("DiscountFactor")
    rs.MoveLast
    Debug.Print "Last", rs.Fields("ZeroCurveID"), rs.Fields("MaturityDate"), rs.Fields("ZeroRate"), rs.Fields("DiscountFactor")
    Debug.Print "There are " & rs.RecordCount & " records and " & rs.Fields.Count & " fields."

    Dim BucketTermAmt As Long
    Dim BucketTermUnit As String
    Dim BucketDate As Date
    Dim MarkAsOfDate As Date
    Dim InterpRate As Double
    Dim I As Integer

    BucketTermAmt = 3
    BucketTermUnit = "m"
    BucketDate = DateAdd(BucketTermUnit, BucketTermAmt, MaxOfMarkAsofDate)
    InterpRate = CurveInterpolateRecordset(rs, BucketDate)
    Debug.Print BucketDate, InterpRate




End If
End Sub

表 VolatilityOutput 包含具有关联值的日期列表。此代码使用表中找到的最接近的值插入给定 MaxofMarkAsofDate 的值。

现在，我从表中选择了 MaxofMarkAsofDate 作为 strSQL 语句的一部分。该代码返回 #7/22/2015# 的正确值。

但是，我需要获取 #7/22/2015# 之前的 76 个日期的值。我可以通过为每个日期手动输入 #7/21/2015#、#7/20/2015# 等来完成此操作。如果可能的话，我想以更快的方式做到这一点。我想使用循环，但我不知道如何将循环与 Access 中的 strSQL 语句组合起来。

Answer 1

CurveID = 15

Dim i As Integer    
For i = 0 to 76
    MaxOfMarkAsofDate = #7/22/2015# - i

    'Your inner code here '
Next i

End Sub