Date + months + 30 days 1

[MICKI0220]

Does anyone know how to code a date calculations of months + 30 days grace period. I am trying to calculate end of commissions on a contract. For example. The contract date starts 6/1/2008. If we pay commissions for 24 months on this type of contract and we have a grace period of 30 days, the date the commission ends should be 6/30/2010. Any help coding this would be appreciated.

Thanks

 

[strongm]

what have you tried?

 

[randy700]

Wouldn't 30 days after 6/1/2010 be 7/1/2010?


Randy

 

[PWise]

dateadd("m",24,contractdate)+dateadd("d",30,dateadd("m",24,contractdate))

 

[MICKI0220]

strongm

I have tried.
DoCmd.RunSQL ("Update tbl_ats_CommissionWork Set DayCommEnds = dtContractStart_dt + ((NumOfMonths)*30)")

 

[MICKI0220]

PWise

I tried this and get a Compile erro, syntax error.

DoCmd.RunSQL ("Update tbl_ats_CommissionWork Set DayCommEnds = dateadd('m', 24, dtContractdate)+ dateadd('d',30,dateadd('m',24,dtContractdate))

 

[MICKI0220]

PWise


What does the 24 mean in your example?

 

[PWise]

Sorry sb just this
also missing 1 closeing prenacies
dateadd("d",30,dateadd("m",24,contractdate)))

 

[MICKI0220]

PWise
I coded this:

DoCmd.RunSQL ("Update tbl_ats_CommissionWork Set DayCommEnds = dateadd("d",30,dateadd("m",24,contractdate)))

I get this:

Compile Error
Expected List Separator or )


Thanks for your help.

 

[MICKI0220]

PWise

24 in my example is not static it is the value assigned to "NumOfMonths" it gets the value from whatever the field "NumOf Months" has in it....It could be 24, 1 999 etc. so the original code I tried that comes short of the date is
DoCmd.RunSQL ("Update tbl_ats_CommissionWork Set DayCommEnds = dtContractStart_dt + ((NumOfMonths)*30)")

 

[PHV]

What about this ?

DoCmd.RunSQL "UPDATE tbl_ats_CommissionWork SET DayCommEnds=30+DateAdd('m',NumOfMonths,dtContractStart_dt)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

MICKI0220,
did you try my suggestion ?

 

[MICKI0220]

yes that worked also. Sorry I am slow on updating this week.