If then Statement

[Ivancho]

Hey guys!
I have a problem, so I'm trying to determine when is the next service date is but I cant find the solution.

I have a table with these fields:ServiceTerm, ServiceDate and NextServiceDate

What I need is to be able to select a service term: Monthly, Bimonthly, Quarterly, Bi quarterly, twice a year, Bi Weekly and Once.

and depending on the selection the next service date would be from their service date.

exp; If Monthly is selected then Next service date will be a month from service date.

I have tried:

IIf([ServiceTermID]="Monthly",DateAdd("m",1,[Servicedate]),
IIf([ServiceTermID]="Bimonthly",DateAdd("m",2,[ServiceDate]),
IIf([ServiceTermID]="Biweekly",DateAdd("w",2,[ServiceDate]),
IIf([ServiceTermID]="Quarterly",DateAdd("m",3,[ServiceDate]),
IIf([ServiceTermID]="TwiceYear",DateAdd("m",6,[ServiceDate]),
IIf([ServiceTermID]="Yearly",DateAdd("m",12,[ServiceDate])))))))​

But no luck, Can anybody please help!!

 

[Beetle]

You wouldn't store the NextServiceDate in the table, it's a calculated value that would be best handled in a calculated query field or a calculated control on a form/report.

I also might suggest that in the table where you store your values for the Service Terms (I'm assuming it's a table. It could be a Value List I suppose but a table would be better), that you also store a value for the approximate number of days for each term. This might look like;

TermValue---Description
14-----------Bi Weekly
30-----------Monthly
60-----------Bi Monthly
90-----------Quarterly
180----------TwiceYear
365----------Yearly

Then the calculation would be simpler;

DateAdd("d",[TermValue],[ServiceDate])

 

[Ivancho]

You are good! Thank you Thank you Thank you!!!!