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.

problem updating text datatype

Hi All:
I am having trouble updating a TEXT column (I know that TEXT is a blob but I
am dealing with very large amounts of data that VARCHAR can not hold!) What
I want to happen is all rows from TableA that meet a certain criteria to be
inserted into TableB but if it already exists in TableB then update it
instead - this sounds simple enough and is working fine except that one of
the fields is TEXT datatype. So when I try to update I get an error "The
text, ntext, and image datatypes are invalid in this subquery or aggregate
expression." (Apparently you can not do an update select with a text
datatype) I did a search and found that I need to be using the UPDATETEXT
function - however I am not quite sure how this works. Any suggestions
would be greatly appreciated!
TableA
ID INT
MenuID INT
Content TEXT
TableB
ID INT
MenuID INT
Content TEXT
Thanks!Nancy,
Without seeing the query, it's impossible to know what the
problem is. Post the query that is causing the error, at
least, if you want a more specific answer.
In any case, it is certainly possible to update [text]
with an UPDATE statement. In general, you may want
something like this:
update TableB set
Content = TableA.Content
from TableA join TableB
on TableA.ID = TableB.ID
and TableA.MenuID = TableB.MenuID
insert into TableB(ID, MenuID, Content)
select ID, MenuID, Content
from TableA
where not exists (
select * from TableB as B2
where B2.ID = TableA.ID
and B2.MenuID = TableA.MenuID
)
Steve Kass
Drew University
Nancy Shelley wrote:

> Hi All:
> I am having trouble updating a TEXT column (I know that TEXT is a blob but
I
> am dealing with very large amounts of data that VARCHAR can not hold!) Wha
t
> I want to happen is all rows from TableA that meet a certain criteria to
be
> inserted into TableB but if it already exists in TableB then update it
> instead - this sounds simple enough and is working fine except that one of
> the fields is TEXT datatype. So when I try to update I get an error "The
> text, ntext, and image datatypes are invalid in this subquery or aggregate
> expression." (Apparently you can not do an update select with a text
> datatype) I did a search and found that I need to be using the UPDATETEXT
> function - however I am not quite sure how this works. Any suggestions
> would be greatly appreciated!
>
> TableA
> ID INT
> MenuID INT
> Content TEXT
> TableB
> ID INT
> MenuID INT
> Content TEXT
> Thanks!
>|||Hi Steve:
Thanks for the quick response. Supplying BLOB columns with text or image
data that's less than or equal to 8000 bytes in size is as straightforward
as updating any other type of column (you can use insert/update) but when
values are larger than 8000 you need to use updatetext or writetext. This
is what I was having trouble with - I know the syntax for updating my table
the regular way I just wasn't sure about using updatetext or writetext.
I ended up solving my own problem - my pointer was invalid. I was getting
an invalid pointer reference to the text column.
Thanks for your help!
Nancy
"Steve Kass" <skass@.drew.edu> wrote in message
news:OqsmT2jWFHA.3488@.tk2msftngp13.phx.gbl...
> Nancy,
> Without seeing the query, it's impossible to know what the
> problem is. Post the query that is causing the error, at
> least, if you want a more specific answer.
> In any case, it is certainly possible to update [text]
> with an UPDATE statement. In general, you may want
> something like this:
>
> update TableB set
> Content = TableA.Content
> from TableA join TableB
> on TableA.ID = TableB.ID
> and TableA.MenuID = TableB.MenuID
> insert into TableB(ID, MenuID, Content)
> select ID, MenuID, Content
> from TableA
> where not exists (
> select * from TableB as B2
> where B2.ID = TableA.ID
> and B2.MenuID = TableA.MenuID
> )
> Steve Kass
> Drew University
> Nancy Shelley wrote:
>|||Nancy,
I believe the syntax I suggested works for any length [text] or
[image] column.
You can also update or insert data into a text or image column if you
provide
it as a literal string or binary value. The 8000 byte limit does not
apply to
literals.
SK
Nancy Shelley wrote:

>Hi Steve:
>Thanks for the quick response. Supplying BLOB columns with text or image
>data that's less than or equal to 8000 bytes in size is as straightforward
>as updating any other type of column (you can use insert/update) but when
>values are larger than 8000 you need to use updatetext or writetext. This
>is what I was having trouble with - I know the syntax for updating my tabl
e
>the regular way I just wasn't sure about using updatetext or writetext.
>I ended up solving my own problem - my pointer was invalid. I was getting
>an invalid pointer reference to the text column.
>Thanks for your help!
>Nancy
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OqsmT2jWFHA.3488@.tk2msftngp13.phx.gbl...
>
>
>