在Access VBA中的strSQL中循环遍历日期 [英] Looping Through Dates in strSQL in Access VBA

问题描述

我在Access中有以下代码:

I have this code in 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值进行插值.

The table VolatilityOutput has a list of dates with an associated value. This code interpolates a value for a given MaxofMarkAsofDate using the closest values found in the table.

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

Right now, I have MaxofMarkAsofDate selected from the table as part of an strSQL statement. The code returns the correct value for #7/22/2015#.

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

However, I need to get the values for the 76 dates prior to #7/22/2015#. I can accomplish this by manually entering #7/21/2015#, #7/20/2015# etc for each date. I would like to do this a faster way if possible. I want to use a loop however I don't know how to combine loops with strSQL statements in Access.

推荐答案

CurveID = 15

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

    'Your inner code here '
Next i

End Sub

这篇关于在Access VBA中的strSQL中循环遍历日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！