These statements are very simple to execute, and even perfect. The majority of the problems that are probably posted on this board seem to exist because nested statements are being used inside this function. Here are some tips on how to keep things strait and organized:


  • First of all, IIF() has 3 arguments, nothing more: IIF(Evaluated expression, Value If True, Value If False)


So by itself, without nested statements or expressions, it's pretty self-explanatory. But now take the following example and see if you can follow it:

Code:
tFactor = IIf(tFactor = "Weekly", "ww", _
          IIf(tFactor = "Monthly", "m", _
          IIf(tFactor = "Quarterly", "q", _
          IIf(tFactor = "Yearly", "yyyy", "yyyy"))))
The above equality is simply constructed of nested IIF() functions inside all the Value If False arguments. In all, there are 4 IIF() functions present. Now go a step further:



Code:
      PeriodEnd = IIf(tFactor = "yyyy", _
                      DateAdd(tFactor, CDbl(ctr + 1), "1/1/" & DatePart("yyyy", prevDate)) - 1, _
                      DateAdd(tFactor, CDbl(DatePart(tFactor, prevDate) + ctr), "1/1/" & DatePart("yyyy", prevDate))) - 1
That equality is a little tougher because you have more functions inside of the primary (and only) IIF() function. One of the most common problems here is getting lost in all of the nested parenthesis () and arguments for the various functions. But if you combine the two above examples, it gets a lot worse:

Code:
PeriodEnd = IIf(tFactor = "Weekly", _
                (DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate)) & "/2000") - 1, _
            IIf(tFactor = "Monthly", _
                (DateAdd(tFactor, CDbl(ctr + 1), DatePart("m", prevDate)) & "/1/2000") - 1, _
            IIf(tFactor = "Quarterly", _
                (DateAdd(tFactor, CDbl(ctr + 1), "1/1/" & DatePart("yyyy", prevDate))) - 1, _
            IIf(tFactor = "Yearly", _
                (DateAdd(tFactor, CDbl(ctr + 1), "1/1/" & DatePart("yyyy", prevDate))) - 1, _
            PeriodStart))))
So how do you not get lost? Make it consistent. Notice in the 3rd code block above, I've indented my Value If True arguments and moved my nested IIF() functions down one line. This way, I can discern when the next nesting starts, plus it's just easier to read. This gets even tougher though, if you have inconsistent nesting needs. Consider the above code, modified a bit:

Code:
PeriodEnd = IIf(tFactor = "Weekly", _
                IIf(DatePart("ww", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate) & "/2010") - 1))) = 52, _
                    "1/1/2011", _
                    "1/" & DatePart("ww", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate)) & "/2010") - 1)) & "/2010"), _
            IIf(tFactor = "Monthly", _
                IIf(DatePart("m", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate) & "/2010") - 1))) = 12, _
                    "1/1/2011", _
                        IIf(DatePart("m", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate) & "/2010") - 1))) = 1, _
                            "2/1/2011", _
                            DatePart("m", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate)) & "/2010") - 1)) & "/1/2010")), _
            IIf(tFactor = "Quarterly", _
                IIf(DatePart("q", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate) & "/2010") - 1))) = 4, _
                    "1/1/2011", _
                    "1/1/" & DatePart("yyyy", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate)) & "/2010") - 1))), _
            IIf(tFactor = "Yearly", _
                IIf(DatePart("yyyy", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate) & "/2010") - 1))) = 2010, _
                    "1/1/2011", _
                    "1/1/" & DatePart("yyyy", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate)) & "/2010") - 1))), _
            PeriodStart))))
Notice that in the above block, the first nested IIF() function has 2 more nests under it, whereas the rest of the top-level nestings only have 1 nest under them. If you're lucky enough to deal with apps where these things are necessary (which they shouldn't really ever be), indentations are your friend. The other thing to do too, if at all possible, is to assign expressions to variables whenever possible. It doesn't matter if you have 50 variables. Nests are easier to read with variables than expressions. So in the last example above, I can assign almost everything to variables, use them, and comment them out so I know what I'm looking at:

Code:
PrevWeekDate = ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate)) & "/2010") - 1) 'Date of 7 days ago

WeekOfYear = DatePart("ww", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate) & "/2010") - 1))) 'this week
MonthOfYear = DatePart("m", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate) & "/2010") - 1))) 'this month
QuarterOfYear = DatePart("q", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate) & "/2010") - 1))) 'this quarter
YearOfYear = DatePart("yyyy", ((DateAdd(tFactor, CDbl(ctr + 1), "1/" & DatePart("ww", prevDate) & "/2010") - 1))) 'this year

PeriodEnd = IIf(tFactor = "Weekly", _
                IIf(WeekOfYear = 52, _
                    "1/1/2011", _
                    "1/" & DatePart("ww", PrevWeekDate) & "/2010"), _
            IIf(tFactor = "Monthly", _
                IIf(MonthOfYear = 12, _
                    "1/1/2011", _
                        IIf(MonthOfYear = 1, _
                            "2/1/2011", _
                            DatePart("m", PrevWeekDate) & "/1/2010")), _
            IIf(tFactor = "Quarterly", _
                IIf(QuarterOfYear = 4, _
                    "1/1/2011", _
                    "1/1/" & DatePart("yyyy", PrevWeekDate)), _
            IIf(tFactor = "Yearly", _
                IIf(YearOfYear = 2010, _
                    "1/1/2011", _
                    "1/1/" & DatePart("yyyy", PrevWeekDate)), _
            PeriodStart))))
Much easier! It becomes extremely efficient in terms of the amount of text required inside of parenthesis (). By the way, the data used in this thread is for illustration. More than likely it will not work if you copy it to your own modules.