从日期列表中提取日期，并根据VBA访问条件打开/关闭参数

Question

DateArray = Array("29/12/2017", "29/06/2018", "31/07/2018", "31/08/2018", "28/09/2018", "31/10/2018", "30/11/2018", "31/12/2018", "31/01/2019", "28/02/2019", "29/03/2019", "30/04/2019", "31/05/2019", "28/06/2019", "31/07/2019", "30/08/2019", "30/09/2019", "31/10/2019", "29/11/2019", "31/12/2019")

L_Date = CDate("11/07/2018")
Y_Date = CDate("10/07/2018")

For Each e In DateArray
    If Month(CDate(e)) = (Month(L_Date) - 1) And Year(CDate(e)) = (Year(L_Date)) Then
        ME_Date = CDate(e)
    ElseIf Month(CDate(e)) = 12 And Month(L_Date) = 1 And Year(CDate(e)) = (Year(L_Date) - 1) Then
        ME_Date = CDate(e)
    Exit For
    End If
Next e

For Each e In DateArray
    If Month(CDate(e)) = 12 And Year(CDate(e)) = (Year(L_Date) - 1) Then
        YE_Date = CDate(e)
    Exit For
    End If
Next e

For Each e In DateArray
    MECheck = False
    YECheck = False
    If StrComp(CDate(e), CDate(Y_Date)) = 0 Then
        MECheck = True
        If Month(CDate(Y_Date)) = 12 Then
            YECheck = True
        End If
        Exit For
    End If
Next e

• me_date=31/10/2018（上个月结束日期）
• ye_date=29/12/2017（上年末日期）
• mecheck=False（因为Y_Date不是30/11/2018）
• yecheck=False（因为Y_Date不是31/12/2018）

谢谢你的帮助，任何建议都非常感谢。

Answer 1

如果您正在使用Access（根据您的问题标记，您是Access），我会强烈指出，您将自定义日期存储在表中，而不是硬编码在数组中。这样，更新日期就更容易了，而且还可以从日期中运行报告。

关于你的评论

这就是为什么我要对手动填充的日期数组进行检查（我不必担心创建一个避免假日/周末的函数）

我认为，从长远来看，编写函数来跳过周末和假期实际上更容易。我会从创建一张桌子来存储你的假期开始。然后编写一个类似于下面的函数来检查给定的日期是否是假日：

' this function uses the Holidays table ('tHolidays')
' to see if a provided date is a holiday
' uses DLookup rather than parameterized query for speed
Function IsHoliday(ByVal MyDate As Date) As Boolean
   Dim sFilter As String
   Dim record As Variant

   sFilter = "HolidayDate = #" & MyDate & "#"
   record = DLookup("HolidayDate", "tHolidays", sFilter)
   IsHoliday = Not IsNull(record)
End Function

检查给定日期是否为周末的函数也非常简单：

Function IsWeekendDay(ByVal MyDate as Date, _
        optional weekendDay1 as VbDayOfWeek = vbSaturday, _
        optional weekendDay2 as VbDayOfWeek = vbSunday) _
as boolean

    Dim weekday as Integer

    weekday = datepart("w", MyDate)
    IsWeekendDay = (weekday = weekendDay1) Or (weekday = weekendDay2)

End Function

Function GetYearEnd(pYear as Integer) as Date
    GetYearEnd = DateSerial(Year:=pYear, Month:=12, Day:=31)

    Do Until Not (IsHoliday(GetYearEnd) or IsWeekendDay(GetYearEnd))
        GetYearEnd = GetYearEnd - 1
    Loop
End Function

Function GetMonthEnd(pYear as Integer, pMonth as Integer)
    ' start with the 1st of the month
    GetMonthEnd = DateSerial(Year:=pYear, Month:=pMonth, Day:=1)

    ' the last calendar day of the month is 
    ' 1 less than the first day of the next month
    ' this approach means we won't have to worry about leap years
    GetMonthEnd = DateAdd("m", 1, GetMonthEnd) - 1

    Do Until Not (IsHoliday(GetMonthEnd) Or IsWeekendDay(GetMonthEnd))
        GetMonthEnd = GetMonthEnd - 1
    Loop
End Function