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

Stumped on Database field calculation

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

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 )