包括 8/4 的前一周数据 VBA 代码

Question

我每周运行此代码，并尝试从 8/4 检索数据直到当前。8月份是报告上出现前一周的月份，但现在是9月份，8月份的数据消失了。您能否帮助我指出更改代码以包含从 8/4 开始的每个前一周数据的方向？

Function getDates(Optional forDate As String = "1/31/1999") As String Dim rst As Recordset
getDateRange = vbNullString

If forDate = "1/31/1999" Then
forDate = DateAdd("d", -1, Date)
End If
If DatePart("d", DateAdd("d", 1, forDate)) = 1 Then
eom = True
End If
Set rst = CurrentDb.OpenRecordset("SELECT Day, FiscalMonth, FiscalYear from tbl_Calendar where FiscalYear = (SELECT FIscalYear from tbl_Calendar where day = #" & _
Format(forDate, "Short Date") & "#) and FiscalMonth = (SELECT FiscalMonth from tbl_Calendar where day = #" & _
Format(forDate, "Short Date") & "#)")

If rst.EOF Then Exit Function
rst.MoveLast
rst.MoveFirst
getDates = CStr(rst.Fields("day").Value) & ";"
rst.MoveLast
If DateDiff("d", rst.Fields("day").Value, forDate) < 0 Then
eom = "False"
Else
eom = rst.Fields("FiscalMonth").Value & ", " & rst.Fields("FiscalYear").Value
End If
getDates = getDates & IIf(DateDiff("d", rst.Fields("day").Value, forDate) < 0, CStr(forDate), CStr(rst.Fields("day").Value)) & ";" & eom
rst.Close
Set rst = Nothing
End Function

Answer 1

首先，始终将日期处理为日期，而不是字符串，不是数字，没有例外。

然后，要查找上周的主要内容，您可以使用：

PrimoPreviousWeek = DateWeekFirst(DateAdd("ww", -1, Date()))

用：

Public Function DateWeekFirst( _
ByVal datDate As Date, _
Optional ByVal lngFirstDayOfWeek As VbDayOfWeek = vbUseSystemDayOfWeek) _
As Date
' Returns the first date of the week of datDate.
' lngFirstDayOfWeek defines the first weekday of the week.
DateWeekFirst = DateAdd("d", vbSunday - Weekday(datDate, lngFirstDayOfWeek), datDate)
End Function

此外，对日期值使用字符串表达式的特定格式：

Set rst = CurrentDb.OpenRecordset("SELECT Day, FiscalMonth, FiscalYear from tbl_Calendar where FiscalYear = (SELECT FIscalYear from tbl_Calendar where day = #" & _
Format(forDate, "yyyy/mm/dd") & "#) and FiscalMonth = (SELECT FiscalMonth from tbl_Calendar where day = #" & _
Format(forDate, "yyyy/mm/dd") & "#)")

然后继续完全重写代码以使用数据类型Date且不使用字符串。