Access 2010 - 三月和十月的最后一个星期日

Question

我有一系列日期，以BST和GMT记录(取决于一年中的时间(。 我希望它们都处于格林威治标准时间，但我无法弄清楚如何让 Access 返回 3 月的最后一个星期日(当我们从 GMT 切换到 BST 时(和 10 月的最后一个星期日(当我们切换回来时(。

所有的帮助感谢！

我在 Access 2010 工作。

Answer 1

您可以使用此泛型函数：

' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
'
' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed.
' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed.
'
' If Weekday is invalid or not specified, the weekday of DateInMonth is used.
'
' 2016-06-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWeekdayInMonth( _
    ByVal DateInMonth As Date, _
    Optional ByVal Occurrence As Integer, _
    Optional ByVal Weekday As VbDayOfWeek = -1) _
    As Date
    Const DaysInWeek    As Integer = 7
    Dim Offset          As Integer
    Dim Month           As Integer
    Dim Year            As Integer
    Dim ResultDate      As Date
    ' Validate Weekday.
    Select Case Weekday
        Case _
            vbMonday, _
            vbTuesday, _
            vbWednesday, _
            vbThursday, _
            vbFriday, _
            vbSaturday, _
            vbSunday
        Case Else
            ' Zero, none or invalid value for VbDayOfWeek.
            Weekday = VBA.Weekday(DateInMonth)
    End Select
    ' Validate Occurence.
    If Occurrence <= 0 Then
        Occurrence = 1
    ElseIf Occurrence > 5 Then
        Occurrence = 5
    End If
    ' Start date.
    Month = VBA.Month(DateInMonth)
    Year = VBA.Year(DateInMonth)
    ResultDate = DateSerial(Year, Month, 1)
    ' Find offset of Weekday from first day of month.
    Offset = DaysInWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysInWeek) Mod DaysInWeek
    ' Calculate result date.
    ResultDate = DateAdd("d", Offset, ResultDate)
    If Occurrence = 5 Then
        ' The latest occurrency of Weekday is requested.
        ' Check if there really is a fifth occurrence of Weekday in this month.
        If VBA.Month(ResultDate) <> Month Then
            ' There are only four occurrencies of Weekday in this month.
            ' Return the fourth as the latest.
            ResultDate = DateAdd("d", -DaysInWeek, ResultDate)
        End If
    End If
    DateWeekdayInMonth = ResultDate
End Function

然后：

LastSundayMarch = DateWeekdayInMonth(DateSerial(Year(SomeDateOfYear), 3, 1), 5, vbSunday)
LastSundayOctober = DateWeekdayInMonth(DateSerial(Year(SomeDateOfYear), 10, 1), 5, vbSunday)

或作为函数：

Public Function DateLastSundayMarch(ByVal DateOfYear As Date) As Date
    DateLastSundayMarch = DateWeekdayInMonth(DateSerial(Year(DateOfYear), 3, 1), 5, vbSunday)
End Function
Public Function DateLastSundayOctober(ByVal DateOfYear As Date) As Date
    DateLastSundayOctober = DateWeekdayInMonth(DateSerial(Year(DateOfYear), 10, 1), 5, vbSunday)
End Function

现在，您可以拥有如下表达式：

=DateLastSundayOctober([SomeDateField])

用作控件的控件源或在 GUI 查询设计器中。

Answer 2

这将起作用：

Option Explicit
Function getLastSundayOfMarchOfThisYear() As Date
    getLastSundayOfMarchOfThisYear = getLastSundayOfMonthOfThisYear(3)
End Function
Function getLastSundayOfOctoberOfThisYear() As Date
    getLastSundayOfOctoberOfThisYear = getLastSundayOfMonthOfThisYear(10)
End Function
Private Function getLastSundayOfMonthOfThisYear(month As Long) As Date
    Debug.Assert month >= 1 And month <= 12
    Dim i           As Long, _
        tmpDate     As Date, _
        daysInMonth As Long
    daysInMonth = Day(DateSerial(year(Date), month + 1, 1) - 1)
    For i = daysInMonth To 1 Step -1
        tmpDate = CDate(year(Date) & "-" & month & "-" & i)
        If Weekday(tmpDate) = vbSunday Then
            getLastSundayOfMonthOfThisYear = tmpDate
            Exit Function
        End If
    Next
End Function