Conditional Formatting Question

I have a column that contains either a test score or the state abbreviation where the student passed a particular test. I want the text to be red between 100-159, green greater than or equal to 160 and black otherwise.

This is what I've tried to take care of the scores:

=IIf(Fields!Score.Value <= 159 and Fields!Score.Value > 100,"Red", "Green")

Two issues:
I want all other scores (< 100) to be Black and
I'm not sure how to deal with the state abbreviations. I'm assuming this is why I receive this error:

[rsRuntimeErrorInExpression] The Color expression for the textbox ‘Score’ contains an error: Input string was not in a correct format.
Preview complete -- 0 errors, 1 warnings

All data is stored as varchar.

Hello,

Yes, the reason you get that warning is because your field is stored as varchar. I think this will do what you want, you can add additional conditions for different colors.

=Switch(

IsNumeric(Fields!Score.Value) and cInt(Fields!Score.Value) >= 160, "Green",

IsNumeric(Fields!Score.Value) and cInt(Fields!Score.Value) > 100, "Red",

1=1, "Black"

)

Jarret

|||Thanks. That did it.

Concatinate without Nulls

I am building a view and I want to return a combined field of all that make
up a full address (Address1, Address2, City, State and ZipCode). If any of
the fields are Null it returns a null. Do I have to use something like
COALESCE on each one as any of them can be null. Thanks.
DavidUse Isnull(field_name, '') to return an empty string from a null.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:#63iepDGFHA.2156@.TK2MSFTNGP10.phx.gbl...
> I am building a view and I want to return a combined field of all that
make
> up a full address (Address1, Address2, City, State and ZipCode). If any
of
> the fields are Null it returns a null. Do I have to use something like
> COALESCE on each one as any of them can be null. Thanks.
> David
>|||>> Do I have to use something like COALESCE on each one as any of them can
That is one simple and recommended way to avoid NULLs being returned.
Anith|||You can also use ISNULL.
Example:
select isnull(lastname + ', ', '') + isnull(firstname)
from employees
go
AMB
"David C" wrote:

> I am building a view and I want to return a combined field of all that mak
e
> up a full address (Address1, Address2, City, State and ZipCode). If any o
f
> the fields are Null it returns a null. Do I have to use something like
> COALESCE on each one as any of them can be null. Thanks.
> David
>
>|||You could also investigate the session option
CONCAT_NULL_YIELDS_NULL
Most client tools set this value to ON to give you the behavior you're
seeing. But if want nulls to be treated as empty strings during
concatenation operations, you can
SET CONCAT_NULL_YIELDS_NULL OFF
The setting (as with all SET options) only applies to the current
connection, or, if you set it in a stored procedure, it applies to that
procedure.
Changing this option will also invalidate the use of indexed views or
indexes on computed columns.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%2363iepDGFHA.2156@.TK2MSFTNGP10.phx.gbl...
>I am building a view and I want to return a combined field of all that make
>up a full address (Address1, Address2, City, State and ZipCode). If any of
>the fields are Null it returns a null. Do I have to use something like
>COALESCE on each one as any of them can be null. Thanks.
> David
>

Concatenating Strings

Suppose that I get a resultset of state abbreviations from dataset. Is there a way to write a code so that final output would be concatenation of states separated by a ",".

I tried maintaining a variable in the "Code" block of the report. Apparently, the delcarations do not work as expected.

I want to do something like this:

Dim str As String

and then str should be updated as each row gets rendered. Is that possible?

Thanks.

You may want to read this blog article about "custom aggregates": http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

-- Robert