IIf Statements with Calculated Fields

[jennifertaylor]

Hello.

I have a simple database that tracks review dates by term. The terms are monthly, quarterly, semi-annual, and annual. I'm having trouble with the following:

I want to be able to create a query with a new field that calculates the next review date by term. For example: if term is monthly, and review date is 1/10/22, my next review date should calculate to 2/10/22, if term is quarterly and review date is 1/10/22, next review date should calculate to 4/10/22, etc.

I have a query created that has fields called NextMonthlyReviewDate, NextQuarterlyReviewDate, NextSemi-AnnualReviewDate, and NextAnnualReviewDate, using these statements: IIf([Term]="Monthly",DateAdd("m",1,[ReviewDate]), [ReviewDate]), IIf([Term]="Quarterly",DateAdd("m",3,[ReviewDate]),[ReviewDate]),

IIf([Term]="Semi-Annual",DateAdd("m",6,[ReviewDate]),[ReviewDate]), and

IIf([Term]="Annual",DateAdd("m",12,[ReviewDate]),[ReviewDate]). How do I just have one field called NextReviewDate field that incorporates all these calculations to return one review date.

[#2]

Hi,

DateAdd("m",Switch([Term]="Monthly",1,[Term]="Quarterly",2,[Term]="Semi-Annual",6,[Term]="Annual",12),[ReviewDate])

Servus
Karl
Access News
Access DevCon

[#3]

That worked! Thank you very much!

[#4]

note, you need to Add 3 (not 2) months to "Quarterly".

also, you will get Error when Term is Blank, so you need another switch:

DateAdd("m",Switch([Term]="Monthly",1,[Term]="Quarterly", 3,[Term]="Semi-Annual",6,[Term]="Annual",12, True, 0),[ReviewDate])

meaning if Term is blank or not in the List, don't add anything (or you have a Default month in mind?)