1 Reply Latest reply on Sep 6, 2012 1:34 PM by philmodjunk

    Stumped on Database field calculation

    RCaauwe

      Title

      Stumped on Database field calculation

      Post

      I have this calc in a field in my database.  It works until it gets to the line where the Dues Year changes from 2012 to 2011.  From there on, the field is blank in those records.  I can't figure out why.  Any help would be appreciated.

      If(Dues Year="2012"; If(Bill Cycle="1"; "1/15/2013";

      If(Dues Year="2012"; If(Bill Cycle="2"; "2/15/2013";

      If(Dues Year="2012"; If(Bill Cycle="3"; "3/15/2013";

      If(Dues Year="2012"; If(Bill Cycle="4"; "4/15/2013";

      If(Dues Year="2012"; If(Bill Cycle="5"; "5/15/2013";

      If(Dues Year="2012"; If(Bill Cycle="6"; "6/15/2013";

      If(Dues Year="2012"; If(Bill Cycle="7"; "7/15/2013";

      If(Dues Year="2012"; If(Bill Cycle="8"; "8/15/2012";

      If(Dues Year="2011"; If(Bill Cycle="9"; "9/15/2012";

      If(Dues Year="2011"; If(Bill Cycle=10; "10/15/2012";

      If(Dues Year="2011"; If(Bill Cycle="11"; "11/15/2012";

      If(Dues Year="2011"; If(Bill Cycle="12"; "12/15/2012";

      "" ))))))))))))))))))))))))

        • 1. Re: Stumped on Database field calculation
          philmodjunk

          I don't think the "nested If's" are set up right.

          IF the year is not 2012, you'll never "get to" a test for 2011 due to how they are nested. To unravel the logic here, try a case function:

          Case ( Dues Year = 2012 And Bill cycle = 1 ; "1/15/2013" ;
                     Dues Year = 2012 and Bill cycle = 2 ; "2/14/2013" ;
                     //and so forth...
                     Dues Year = 2013 and Bill Cycle = 12 ; "12/15/2012
                   )

          But this looks like a case where you do not need if, or case, but simply a straight forward calculation:

          Bill Cycle & "/15/" & Dues Year

          or better yet:

          Date ( Bill cycle ; 15 ; Dues Year )