Saturday, February 11, 2012

#ERROR on SUM if Field value is a string

Hi,

I have some columns that can be either a Number or Text.

and I have to sum all the value if that column is a Number, therefore in my Table footer i have:

=IIF(IsNumeric(Fields!Col1.Value),Sum(Fields!Col1.Value),"")

However this will give me #ERROR if the columns is Text. It works for Number.

Any help is appreciated, thanks

Jon

Try =Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,""))

|||

Thanks for your reply.

unforunately i tried you suggestion but it didn't help, it still gives me #error

any other idea i can try?

thanks

|||

How about:

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,Nothing))

or

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,0))

|||

Thanks!

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,Nothing))

works well for most of them, but Not on columns which are TRUE/FALSE?

|||

Do you mean columns that are declared as a of Bit data type? If so, they are probably passing the IsNumeric test.

|||

Try:

=IIF(IsNumeric(Fields!Col1.Value),Sum(val(Fields!Col1.Value)),0)

Val() function returns the number part of the string.

Somiya.

No comments:

Post a Comment