Incrementing Dates

[derekstewart]

I have a database that contains a contract effective date, and end date and an associate review period, either 90, 180 or 365 days. I have (([enddate]-[effective date[/[perioddays] that tells me how many times to review over the life of the contract. Now I just need to know the expected dates. Getting the first is simple, but I have having trouble getting it to increment to get future dates. Here's an example: I have a contract that starts 4/1/07 and ends 3/31/08. It needs to be reviewed every 90 days. That's 4 periods. I know the first review date should be 6/30/07 but I need it to list all 4 dates. Any thoughts? Thanks. D

 

[adalger]

ReviewDate1 = DateAdd("d",90,DateEffective)
ReviewDate2 = DateAdd("d",90,ReviewDate1)

etc.

TMTOWDI - it's not just for Perl any more

 

[derekstewart]

Thanks Adalger. How do I get it to create the review dates only of the amount of periods I have? I have other contracts that are 5 years long and need to be reviewed every 180 days so that is 10 periods. Each contract has a varying duration and number of periods. Thanks. D

 

[adalger]

I'd use a select case in VBA.

Public Function ReviewFrequency(Term as Integer) as Double
    Select Case Term
    Case <x1> to <x2>
        ReviewFrequency = 90
    Case <x2> to <x3>
        ReviewFrequency = 180
    Case <x3> to <x4>
        ReviewFrequency = 365
    Case Else
        ReviewFrequency = 0
    End Select
End Function

Then review dates are DateAdd("d", ReviewFrequency(<years>),DateEffective) etc.

TMTOWDI - it's not just for Perl any more

 

[anon47]

You can use a monthly bases like so

ReviewDate = DateAdd("m", 1, ReviewDate) "change the 1 to any number of months to postpone the date to. This way you will be reviewing on the same day of the month every month. Hope this helps