Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DateAdd interval (a2k3)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DateAdd interval (a2k3)

    Try replacing

    CurrentDb.Execute "Insert into tblPymt (dtmBillStartDate,dtmBillEndDate) values (",#" & Format(dtmBillStartDate, "mm/dd/yyyy") _
    & "# , #" & Format(dtmCurrentDate, "mm/dd/yyyy"));"

    with

    CurrentDb.Execute "Insert into tblPymt (dtmBillStartDate,dtmBillEndDate) values (",#" & Format(dtmBillStartDate, "mm/dd/yyyy") _
    & "# , #" & Format(dtmCurrentDate - 1, "mm/dd/yyyy"));"

    I assume the code you posted is a simplified version, since as it is, the loop would never end.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateAdd interval (a2k3)

    You've done it again! Thanks.

    It was indeed stripped down code.

    E

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •