Determining Expected Date

[derekstewart]

I have a start date and end date for a contract and need to track the dates a rebate should be sent to us. Rebates can be quarterly, biannually or annually. As an example if we have a contract that starts on Jan 1 2007, ends in March 31, 2009 and has a quarterly rebate I would expect 9 rebates over the term of the contract. I would like to be able to run a report that will show the rebate schedule for the contract. The code below works but only shows the first period.
CODE
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

DateAdd("d", PeriodDays,StartDate)

I would like it to appear like this:

Contract Rebate Expected
Bank Contract 1a March 31, 2007
Bank Contract 1a June 30, 2007
Bank Contract 1a Sept 30, 2007
Bank Contract 2 Jan 1, 2008
Bank Contract 2 Jan 1, 2009

Any thoughts? Thanks. D

 

[CautionMP]

I would like to be able to run a report...

Do you really want an Access Report or some other type of output?

CMP


[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[derekstewart]

I would just like a working query at this point. I can get it into a report after if need be. Thanks. D

 

[CautionMP]

derekstewart,
You could do it as a Cartesian join. This will allow you create a one-to-many relationship for each of your contracts based on the number of periods.

Here's the setup I used.

tblContracts[tt]
[tab]Id
[tab]Start
[tab]End
[tab]Term (4 digit string that corresponds to the arguments for DateDiff()/DateAdd)[/tt]

tblContractPeriods[tt]
[tab]Periods[/tt]

This was my query from the query builder:

SELECT tblContract.ID, tblContract.Start, tblContract.End, tblContract.Term, DateAdd([Term],[Periods],[start]) AS Rebate_Expected
FROM tblContract, tblContractPeriods
WHERE (((tblContractPeriods.Periods)<=DateDiff([Term],[Start],[End])));

And this was the output:
[tt]ID Start End Term Rebate_Expected
Bank Contract 1a 9/30/2006 9/30/2007 q 9/30/2007
Bank Contract 1a 9/30/2006 9/30/2007 q 6/30/2007
Bank Contract 1a 9/30/2006 9/30/2007 q 3/30/2007
Bank Contract 1a 9/30/2006 9/30/2007 q 12/30/2006
Bank Contract 2 7/1/2007 1/1/2009 yyyy 7/1/2009
Bank Contract 2 7/1/2007 1/1/2009 yyyy 7/1/2008[/tt]

Would probably be easier with a report since you won't need to use a trick to create a one-to-many relationship.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

forgot to mention the tblContractPeriods needs to be populated with the number of possible periods. I used 1 - 24 but I would only have needed 1 - 4 for the examples above.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[MichaelRed]

apparently it is acceptable to have the rebates due on non-working days?




MichaelRed

 

[derekstewart]

It does not matter that they fall on non working days. Good point though. This solution worked great. My only question CautionMP is what do I use for the term for 180 days? I use q for 90 and yyyy for annual but what about biannual? Thanks for the help. D

 

[CautionMP]

derekstewart,
Your more than welcome and I'm glad to hear it worked for you. I use cross joins so rarely it's fun to play with one every once in a while.

[tt]DateDiff()[/tt]/[tt]DateAdd()[/tt] only take a limited number of arguments for the interval and semi-annual isn't one of them. I would probably change the [tt]Term[/tt] field from a string to the number of months and then slap together a couple of UDFs to replace [tt]DateDiff()[/tt] and [tt]DateAdd()[/tt] in the query.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[derekstewart]

I actually started working on that when a search on datediff turned up nothing. Got it working perfectly now. Thanks for your help. D