Case Select - i think

[fibayne]

Hi, looking for advise on which direction to take with this, if possible

I have a form with policy start date from which the customer chooses the period to receive payments - Quarterly, Monthly, Half Yearly, Yearly

I would like to select the pmt period and and have the date calculation done in VBA would this be best done using Case Select somehow ???
ie select Quarterly and the forthcoming year pmt dates are displayed, based on the policy start date

thanks as always for any help
cheers

Fi

 

[namliam]

Since all your options are monthly periods... I would add a column to your period field, this presumably would be a lookup table/combobox.

This you can the just fill with the number of months you want to jump ahead.
Then in code either add the months x number of times or do it while the date < 1 year ahead or something simular.

 

[fibayne]

Hi namliam.. thanks for your reply been thinking about what I am trying to do...if i know the Pmt Period and the Startdate of the policy, what I ultimately want to achieve is to run a report with date params which will pick up those policies with a pmt due for whatever Pmt period type within that param, i dont think I want to store all the possible due dates as there could be thousands just want the date calc to be able to tell if a pmt would be due during the parms entered based on the start date and Pmt period ....does this sound possible ??

thanks again for you help...cheers

Fi

 

[namliam]

Thats possible using the same type of setup...

What you would have to do is something like setup the timeperiods to have x number of months... 1,3,6,12 in this case (I think)

Then add "datediff" months (Lookup that function) between your period and the starting time to find the "next" date.

i.e.
Contract start 1-1-2009, period Monthly
today 14 jan 2009, datediff in months = 0
Devide this 0 by the period, in this case 1, in another case 3. Thus producing the number of periods that have passed.
Next payment due: Add "([Periods Passed) + 1) * Period" months to the contract startdate.

I hope that is clear (enough)

 

[fibayne]

Hi namliam...looking at your suggestion now, will post back...with more questions no doubt cheers Fi

 

[fibayne]

Hi namlian...your suggestion works great thank you very much....there had to be a question though ! we would have start dates in the future and it isnt working correctly, I have tried to change the calulation to incorporate this but have not managed to get it working....woudl you be kind ehough to help with this
below is the final calc for next pmt date
=DateAdd("m",([perioddiff]+1)*[periodpassed],[startdate])

many thanks..cheers Fi

 

[namliam]

I would probably solve this by either limiting the query to only return 'current' ones...
Or us an IIF to put in a different formula.

 

[fibayne]

Hi..would you be able to expand on your reply ?? I think I need to increment the dates somehow as all i can get it to ...which is great...is give the next payment date based on start date ie if we look to the future and todays date is 3 months in the past, the monthly period, next pmt due would be 14.02.09 instead of 14.04.09, it needs to refererence to the current date somehow then give the next pmt due, would you able to help further with that ??? thanks again cheers Fi

 

[fibayne]

Hi...been searching posts and trying in vain to work through this but cant work out how to have the NextDate calculate by looking at the startdate then the policyperiod and be able to give a NextDate which is the next due date after todays date for that particular policy, possibly thinking about this all the wrong way any further help would be greatly appreciated...thanks as always cheers
Fi

 

[namliam]

I would probably put something like
Where contractPeriod < Today

Or something like
Where ContractPeriod + PaymentPeriod < today

To get only the ones which are due for a charge/payment...

 

[fibayne]

Hi..thanks for your reply, I am doing this which is giving the answer I need but I dont think it is the best way to do ...I could be wrong also it is ok for the Qtrly, Annual, biannual nextduedate calculation but when it comes to month there would be way too many IIf's
this I hav set as the control source of NextDueDate on the form....

=IIf([perioddiff]=12,DateAdd("m",[periodlu],[cbDate]),IIf([perioddiff]=24,DateAdd("m",[periodlu]*2,[cbDate]),IIf([perioddiff]=36,DateAdd("m",[periodlu]*3,[cbDate]),IIf([perioddiff]=48,DateAdd("m",[periodlu]*4,[cbDate]),IIf([perioddiff]=60,DateAdd("m",[periodlu]*5,[cbDate]),IIf([perioddiff]=72,DateAdd("m",[periodlu]*6,[cbDate])))))))

what do you think ???? cheers Fi

 

[fibayne]

Hi again...on your quote below wouldnt this only show the very first pmt date without taking into consideration that many pmt date could have passed since the start date....although i am probably not understanding what you are getting at

'I would probably put something like
Where contractPeriod < Today

Or something like
Where ContractPeriod + PaymentPeriod < today

To get only the ones which are due for a charge/payment...

cheers Again Fi

 

[namliam]

No you want to make your periodpassed a calculation like I posted earlier... not do it in IIF.... That will go crazy out of controle fast.

 

[fibayne]

Hi...been at this all day and cant get my had round how to do it, self taught and it shows I dont understand what you are suggesting I do as how I understand what you are suggesting doesnt take into account dues dates in the future it will only give the first due date of the policy pmt plan...apologies I am sure its my lack of knowledge would you be able to elaborate on the calculation that you feel would work....thanks again Fi

 

[namliam]

You have a Contract start date (CSD), right??
So time passed is DateDiff("M",Today,CSD)

Now if your Payment period is Yearly, or 12... Deviding time passed by your Payment period will get you periods passed. Integer it to round it down and not get 0.5 periods.
Int( DateDiff("M",Today,CSD) / PaymentPeriod )

Now with this PeriodsPassed (PP) you can calculate when the next payment is due.
DateAdd("M", (PP + 1) * PaymentPeriod, CSD)

Example:
01-Jan-2009 CSD
15-Jan-2009 Today
Period: 1
PP: 0
Next payment date Add (0 + 1) * 1 month(s) to CSD = 01-Feb-2009

15-Feb-2009 today, all others same
PP: 1
Next payment date add (1 + 1) * 1 month(s) to CSD = 01-Mar-2009

etc... etc...

 

[fibayne]

Hi...they way I see it working is NextDueDate is calculated by looking at the PolicyStartDate the PmtFrequency and todays date it would need to calculate all possible NextDueDates that have occurred to date then display the next one dated after todays date which to me sounds like it needs a table of all possibilities ie NextDueDates specific to theat policy and its pmt frequency!!!!! so that it can pick the nearest one after todays date...however putting into practice how i see it working is a whole differant matter...in case you havent noticed Fi

 

[namliam]

What is wrong wit my approach? It is going to be a lot of records to create all possible payments required for the next 10 years for all your customers?

 

[fibayne]

Hi namliam....thanks again for your help, I had this working from your previous post the problem is for future dated CSD's, which is what most will be when set up, in your example if we have
Payment Frequency - Monthly
Today - 16.01.09
CSD - 10.10.09
PP - 9 (not taking into account that the CSD is in the future)
Next Payment Date - 10.08.10 (answer should be 10.11.09)
So I looked at adding in some IIf's this is what i haev and it does seem to be working what do you think ??

PPM is to get round when the CSD is in the past as it will eventualy be, and is CSD-Date rather than Date-CSD

NPD: IIf([CSD]<Date() And [PP]>=0,DateAdd("m",([PP]+1)*[period],[CSD]),IIf([CSD]<Date() And [PP]<0,DateAdd("m",([PPM]+1)*[period],[CSD]),IIf([cbDate]>Date(),DateAdd("m",[period,[CSD]))))

 

[namliam]

So, any Periods Passed that turn out a negative should be 0...

I.e. IIf( CSD > Today, 0, Datediff)

This will product 10-Nov-2009 if you fit it into the formula

 

[fibayne]

Hi..if the CSD turns out to be negative the calc uses PPM (CSD-Today) rather than PP (Today-CSD) making the NPD calc use a positive rather than negative for its calc, I need to see all NPD dates whether the CSD occurs in the past, present or future this calc for example gives me,
Payment Frequency - Monthly
Today - 16.01.09
CSD - 10.10.07
NPD - 01.02.09
which is what i am after, does that look like how you see it working ???
thanks again for following this up...cheers Fi