包括前一周来自8/4未来VBA代码的数据
包括前一周来自8/4未来VBA代码的数据
提问于 2018-09-06 23:47:00
我每周运行一次此代码，并尝试检索从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回答 1
Stack Overflow用户
发布于 2018-09-07 01:57:58
首先，始终将日期处理为日期，而不是字符串，也不是数字，没有例外。
然后，要查找前一周的primo，您可以使用：
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、和no string数据类型。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52207884
复制相关文章
相似问题
腾讯云开发者
