I’ve inherited this function allows you to enter a start date and end date with an interval and it proceeds to generate records of billing periods between the start and end dates with the billing period duration you selected in interval and inserts it into a table.
The way it is currently the start date is the first of the month and the last day of the billing period is the first day of the following month, if the interval is month. This means that the first day of the month can be in two billing periods. The end date of the billing period should be plus 1 month minus 1 day. How do I amend this code to make that correction? Is it as simple as putting -1 behind the DateAdd arguments?
E
Function funGenerateBill(dtmStartDate As Date, dtmEndDate As Date, _
txtDuration As String) As Boolean
Dim strInterval As String
Dim nIncrement As Integer
Dim dtmBillStartDate As Date
Select Case txtDuration
Case “Monthly”
strInterval = “m” ‘ for months
nIncrement = 1 ‘ monthly so this will be 1
Case “Quarterly”
strInterval = “m” ‘ Increment in months
nIncrement = 3 ‘ 3 months for single quarter
End Select
dtmCurrentDate = dtmStartDate
Do
dtmBillStartDate = dtmCurrentDate
dtmCurrentDate = DateAdd(strInterval, nIncrement, dtmCurrentDate)
CurrentDb.Execute “Insert into tblPymt (dtmBillStartDate,dtmBillEndDate) values (“,#” & Format(dtmBillStartDate, “mm/dd/yyyy”) _
& “# , #” & Format(dtmCurrentDate, “mm/dd/yyyy”));”
Loop
End Function
