Forum Discussion

jennifertaylor's avatar
jennifertaylor
Copper Contributor
Jan 20, 2022

IIf Statements with Calculated Fields

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.

      • arnel_gp's avatar
        arnel_gp
        Steel Contributor

        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?)

         

         

Resources