Reccurring Date Issue

[allbookstv]

Hi,
I am designing a customer database which has different types of payment plans, monthly, quartlerly and yearly and each plan can be for a different amount of money. I have set the 27 payment plans up in their own table with an ID Key for each type.

What I would like to do is setup a recurring date for each payment plan so it can bring up a list daily of payments to be taken, ie,

Mr Smith £15 Quarterly, setup date 10/03/06 will display automatically next payment due 10/06/06

As you can see I cannot setup a single DateAdd function as there are many different types of plan. Any help would be greatly appreciated,

regards
Simon

 

[Kelemit]

I'm assuming in your table with the payment plans, you have each one identified by "monthly, quarterly, yearly" etc...

My suggestion, instead of a field with "montly, quarterly, yearly" in the field, put in there the number of months those plans equate to. IE: montly =1, quarterly = 4 yearly = 12.

Then when you want to find out the next payment plan, then you take, last payment + dateadd(field for payment plan in months).

Then have a lookup / dropdown box equating 1, 4, 12 to montly, quarterly, yearly for display purposes in forms / reports (or run sql join for reports...)

Alternatively, you could leave the "payment plan cycle field" as montly, quarerly, yearly and put in a crazy

IIF([payment plan cycle fiel]="Monthly",1,(IIF([payment plan cycle field]="Quarterly",4,12)))

But then, if you ADD ANY other plan to the payment type table ( IE: 2 times a year, 6 times a year, 3 times... 1 every 2 year... whatever) you will have to change the IIF statement for each change.

The first suggestion is the wisest / best. By putting in the number of months to add, you can add as many payment plan types as you want, as long as they do not go less than one month. Then simply have a table that equates the number of months to the nomenclature (naming scheme) that your company does. Having a table for that allows users to add / remove payment plans on the fly, and allowing you database to handle it.

The only thing you have to be aware of is adding a trap for payments started on the 29 - 30. IF the next payment, for example, lands on febuary 31... you will get an error. Either trap it so that dates end up being the 28 everytime a payment is started on the 29 ~ 31, or make it the 1st of the next month. Easiest solution.

Kelemit