ms-access - 每月的 DateAdd 函数丢失月末信息 (Microsoft Access)

Question

首先让我说一下，我对 VBA 和一般编码基本上是个新手。我的公司运行一个 Access 数据库来跟踪我们的大约 500 个存储客户和批处理。每个批处理都有一个支付日期、到期日期和与之相关的到期日期，每月更新一次(通常)。

我们过去只是使用日期选择器在抽取批处理后手动选择每个日期。但是，我创建了一个运行以下命令的按钮:

Private Sub Extend_One_Month_Click()
Me.[Date Paid Actual] = Date
Me.[Date_Paid] = DateAdd("m", 1, [Date_Paid])
Me.[Date_Paid_Expiry] = DateAdd("m", 1, [Date_Paid_Expiry])
Me.Recalc
End Sub

这在大多数情况下都很好用。我的问题是它不保留任何月末信息。因此，如果客户通常的截止日期是每个月的 29 日(或更晚)，而我在 2 月使用此功能，则他们的截止日期将变为每个月的 28 日(无论如何在 Access 上)。是否有任何功能可以保留这些信息？

谢谢!

Answer 1

我的函数 DateAddMonth 正是这样做的:

' Returns a date added a number of months.
' Result date is identical to the result of DateAdd("m", Number, Date1)
' except that if an ultimo date of a month is passed, an ultimo date will
' always be returned as well.
' Optionally, days of the 30th (or 28th of February of leap years) will
' also be regarded as ultimo.
'
' Examples:
'   2020-02-28, 1, False -> 2020-03-28
'   2020-02-28, 1, True  -> 2020-03-31
'   2020-02-28,-2, False -> 2019-12-28
'   2020-02-28,-2, True  -> 2019-12-31
'   2020-02-28, 4, False -> 2020-06-28
'   2020-02-28, 4, True  -> 2020-06-30
'   2020-02-29, 4, False -> 2020-06-30
'   2020-06-30, 2, False -> 2020-08-31
'   2020-06-30, 2, True  -> 2020-08-31
'   2020-07-30, 2, False -> 2020-08-30
'   2020-07-30, 1, True  -> 2020-08-31
'
' 2015-11-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateAddMonth( _
    ByVal Date1 As Date, _
    Number As Double, _
    Optional Include2830 As Boolean) _
    As Date
    
    Dim DateNext    As Date
    
    ' Add number of months the normal way.
    DateNext = DateAdd("m", Number, Date1)
    If Day(Date1) = MaxDayValue Then
        ' Resulting day will be ultimo of the month.
    ElseIf IsDateUltimoMonth(Date1, Include2830) = True Then
        ' Months are added to month ultimo.
        ' Adjust resulting day to be ultimo if not.
        If IsDateUltimoMonth(DateNext, False) = False Then
            ' Resulting day is not ultimo of the month.
            ' Set resulting day to ultimo of the month.
            DateNext = DateThisMonthUltimo(DateNext)
        End If
    End If
    
    DateAddMonth = DateNext
    
End Function

它使用了几个辅助函数和常量 - 太多了，无法在这里发布 - 所有这些都可以在我位于 GitHub 的项目中找到:

VBA.Date