Case Select - i think

[namliam]

Except today - CSD would still be 9 months, thus makeing the date be 9 months from CSD instead of 1 month.

Do as I suggested in my previous post, if the CSD > today, use 0 else calculate the difference.

How would you go from CSD 10-10 to NPD 01-02?? I assumed (more or less) you wanted to keep the day in tact and you would go to 10-02-09....

 

[fibayne]

Hi...ok get what you are saying, CSD of today is blank.....would this be close to the statement I need to fix it...(dont think so as this doesnt work )
IIf([CSD]>Date(),0,DateAdd("m",[period],[CSD]
cheers Fi

had another try and think this is working
IIf([CSD]=Date(),DateAdd("m",0,[CSD]

 

[fibayne]

the last one posted doesnt work correctly after further testing
but I think this one does
IIf([CSD]=Date() And [PP]=0,DateAdd("m",[period],[CSD]
cheers
Fi

 

[namliam]

More like so
DateAdd("m",IIf([CSD]>Date(),0,[period]),[CSD])


You want to manipulate the period, not the whole thing.

 

[raskew]

Hi -

Would this be close?

Public Function NextDue(CSD As Date, per As Integer) As Date
'*********************************************
'Re:  http://www.access-programmers.co.uk/forums/showthread.php?t=163707
'Purpose:   Return next payment date following
'           current date, based on a Contract
'           Start Date (CSD) and a period (per)
'           of monthly (1), quarterly (3),
'           semi-annually (6), yearly (12)
'Coded by:  raskew
'Inputs:    1) ? NextDue(#11/15/07#, 1)
'           2) ? NextDue(#11/15/07#, 3)
'           3) ? NextDue(#11/15/07#, 6)
'           4) ? NextDue(#11/15/07#, 12)
'OutPuts:   1) 2/15/2009
'           2) 2/15/2009
'           3) 5/15/2009
'           4) 11/15/2009
'*********************************************

Dim x       As Integer
Dim dteHold As Date

   x = (DateDiff("m", CSD, Date) + (DateSerial(Year(Date), Month(CSD), Day(CSD)) > Date)) \ per
   dteHold = DateAdd("m", (x + 1) * per, CSD)
   NextDue = IIf(dteHold <= Date, DateAdd("m", per, dteHold), dteHold)

End Function

Bob

 

[fibayne]

Hi..been off this 'project' for a few days thanks for your replies will look at them and post back...cheers Fi