IIF Statement not working correctly

[Sherri_(Guest)]

I^m having a problem where the IIF statement doesn^t seem to be working correctly. When I setup my calculation fields like below, and then run the report, MonthP1 is returning "Jan" instead of "Oct". I^ve tried setting Period_1 to different expressions (FiscSep04, FiscNov04, FiscDec04) and the MonthP1 always returns "Jan". Why is it always choosing the first result in the iif statement, even when the test value does not match?____My calculations fields & expressions are listed below.____Calculation field: Period_1__Expression: FiscOct04____Calculation field: MonthP1__Expression: IIF(Period_1=FiscJan04,"Jan", __IIF(Period_1=FiscFeb04,"Feb", IIF(Period_1=FiscMar04,"March", IIF(Period_1=FiscApr04,"April", IIF(Period_1=FiscMay04,"May", IIF(Period_1=FiscJun04,"June", IIF(Period_1=FiscJul04,"June", IIF(Period_1=FiscAug04,"Aug", IIF(Period_1=FiscSep04,"Sept", IIF(Period_1=FiscOct04,"Oct", IIF(Period_1=FiscNov04,"Nov", IIF(Period_1=FiscDec04,"Dec",""))))))))))))____Thanks,__Sherri____

[Rick_Johnson_(Guest)]

This may help. I did not work on it long, but I had to make the value of Period_1 to be "FiscOct04" (note the quotes). I then put quotes around all of the FiscXXX04 in the iif Statement. It worked correctly. Now sure why, but may depend on the definition of FiscOct04.____Also, you may want to try to use the CASE function rather than the nested iif. Just a little easier to read and construct.____

[Sherri_(Guest)]

Unfortunately, putting quotes around Fiscxxx04 did not work. I got the invalid argument error. Fiscxxx04 is also a calculation that contains an expression consiting of a date range. That might make a difference.

[kfleming]

What data type does Fiscxxx04 return?____Kathleen__R&R Support

[Sherri_(Guest)]

Fiscxxx04 is returning data for a particular date range.____So.. if I make calculation field "Period_1" equal to calculation field "FiscJan04" and then I make Period_1 = True, the report will print sales figures for the month of January.____I^m trying to setup an IIF statement (in a calculation field called MonthP1) so that it will print "Jan" if Period_1=FiscJan04, or if Period_1=FiscFeb04, then it MonthP1 will return "Feb", etc.____Does this make sense?

[kfleming]

So are there actually 12 fields whose data type is date?____I am still confused by what you are working with..____Kathleen__R&R Support____

[Sherri_(Guest)]

There are 12 date fields: FiscJan04, FiscFeb04, etc.____There are 3 period fields: Period1, Period2, Period3____These are all calculation fields. FiscJan04 contains the date range for our fiscal January month, FiscFeb04 contains the date range for our fiscal February Month, etc.____In order to get the report to print out multiple periods of data and keep them seperated, I need to seperate the date ranges into Periods (we only have 3 periods print at a time: current month, 1 previous month, 2 previous months).____So, If I were to print a report at the end of this month, and I wanted to see our sales/margins data for the last 3 months, it would set it up like this:____Period1=FiscDec04__Period2=FiscNov04__Period3=FiscOct04____That works find, and all of the numbers print out correctly.____However, I am trying to get the report to simply label each section with the month that is printing. So, I^m trying to create a calcuation field that says:____If Period1 is equal to FiscDec04, then print "December". I^ve set it up like this:__Calcuation Name: MonthP1__Expression:__IIF(Period1=FiscJan04,"January", IIF(Period1=FiscFeb04, IIF(Period1=FiscMar04,"March,....... IIF(Period1=FiscDec04,"December",""))))))))))))____When I try to create the above expression, I get an error message "Invalid Argument" and I can^t figure out why. Does this make sense?____Thanks____

[Sherri_(Guest)]

**Correction to previous posting**____In the very last part of my previous posting, I said I was receiving an error message "Invalid Argument". That is incorrect. What^s happening is that when I set Period1=FiscDec04, it^s suppose to print out "December", but it is actually printing out "January". That happens no matter which Fisc*04 I set Period1, Period2, or Period3 to.____Hope this makes sense.____Thanks

[Sherri_(Guest)]

I solved the problem.. I flipped around the comparrison, and now it works.