DateAdd Function on Monthly Basis Looses End-of-Month Information (Microsoft Access)-CodePudding

Let me preface by saying I'm mostly new to VBA and coding in general. My company runs an Access database to keep track of our ~500 storage customers and lots. Each lot has a date paid, date due, and expiry date associated with it that is updated monthly (usually).

We used to just use the Date Picker to select each date manually after pulling up the lot. However, I've created a button that runs the following command:

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

This works great, for the most part. My issue is that it does not keep any end-of-month information. So, if a customers usual due date is the 29th (or greater) of each month, and I use this function in February, their due date becomes the 28th of each month moving forward (on Access, anyway). Is there any function that will preserve this information?

Thank you!

CodePudding user response：

My function DateAddMonth does exactly that:

' 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

It uses a couple of helper functions and constants - too much to post here - which all can be found in my project at GitHub:

VBA.Date