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...
access
Karl_Donaubauer's avatar
Karl_Donaubauer
MVP
Jan 20, 2022

Hi,

 

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

 

Servus
Karl
Access News
Access DevCon

  • jennifertaylor's avatar
    jennifertaylor
    Copper Contributor
    Jan 20, 2022
    That worked! Thank you very much!
    • arnel_gp's avatar
      arnel_gp
      Iron Contributor
      Jan 21, 2022

      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