Problem using Monthname function

I am trying to have the below formula populate a portion of a textbox

The right(Parameters!ToTimeDimensionCloseYYYYMM.label,2) is because I am dealing with "YYYYMM" and I only want the "MM" portion.

=iif(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2)=01,Monthname(12),monthname(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2)))

When I use this formula it will work for December, however for the rest of the months if a user chose 200603, it will read "March" when I really want it to read "February".

=iif(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2)=01,Monthname(12),monthname(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2)-1)))

However, when I try and write the formula I really want (above), it will work for February through December (giving me the prior month), but if a user chose 200601 (January) as their last Month, it reads "#ERROR", instead of "December"

The error it give me is = [rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox1’ contains an error: Argument 'Month' is not a valid value.

I have tried several different "versions" of this, including just telling it to put "December", putting "last" in front of the monthname instead of "-1", trying to use "Previous" (which I don't understand but doesn't seem to work either), to try to get it to recognize that I want it to give me the previous month when the user selects the current month, with no success. It seems as if the "-1" at the end causes it to break, because if I take the "-1" out, it works fine except I don't get the previous month for February through December.

Monthname needs an Integer parameter, so try

=iif(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2)="01",Monthname(12),monthname(Cint(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2))-1))

Regards

Ayzan

|||

Thanks Ayzan, but it still gives me the same thing:

If the parameter selected = 200602, then it gives me January;

If the parameter selected = 200607, then it gives me June,

But as soon as the parameter selected = 200601, it gives me "#ERROR" instead of December, with the same message that Argument 'Month' is not a valid expression.

It's almost as if it doesn't even recognize the first part of the expression with the -1 in it...b/c if I take out the -1 then it give me December for 200601, but then it gives me "February" for 200602 and "July" for 200607 when I really want "January" and "June".

Thanks again for your help...any additional help would be greatly appreciated.

|||

Create an instance of the DateTime struct and use the .NET functions it has to perform date arithmetic

=MonthName(
new DateTime(
CInt( Left( Parameters!ToTimeDimensionCloseYYYYMM.Label, 4 ) ) //Year
, CInt( Right( Parameters!ToTimeDimensionCloseYYYYMM.Label, 2 ) ) //Month
, 1 //Day
).AddMonths( -1 ).Month //Subtract a month and then access the Month Property
)

|||

If you find it runs slowly then you could use an Iif statement as follows:

=MonthName(
Iif(
CInt( Right( Parameters!ToTimeDimensionCloseYYYYMM.Label, 2 ) ) = 1
, 12
, CInt( Right( Parameters!ToTimeDimensionCloseYYYYMM.Label, 2 ) ) - 1
)
)

|||Adam, Thank you so much!!!! I used the iif statement as I am combining it with other Text and it works perfectly.