IIF(IsNothing(Fields!field.value), "", Fields!.value.ToString)) generates error

Question

SQL Server 2005 reporting services.

I want to using the following in a report text box:

=IIF(IsNothing(Fields!Certification.Value), "", 
    "Certs: " + Fields!Certification.Value.ToString() )

But because both sides (T/F) are evaluated, I get an error on the report when the field is null. How can I get around this?

Solution 1

Use below expression instead

=IIF(IsNothing(Fields!Certification.Value),nothing,"Certs: " & CStr(Fields!Certification.Value))

Solution 2

Another way to get around this situation would be to do an ISNULL(Certification,'') in the SQL Query. This is assuming of course that you are querying SQL Server.

SELECT ISNULL(Certification,'') as Certification 
FROM YourTableName

This way any NULL's appearing in the result-set will be removed.

Solution 3

Is Certification Field a Calculated field or Database field?

if it is Calculated Field then check again your statement.

Cause I do not have to use 'IIF' even when the record contains null value, just Fields!Certification.Value on textbox

Comments

[CW1255]

It's a DB field result. Either a string value or NULL. The above answers worked. I didn't think you could do CStr(NULL). I also found another way around. I created a hidden string parameter, Allowed NULL & Blank values. Available Values: from Query - I picked the ValueField. Default Values: From Query - I picked the Value Field & used: =IIF(IsNothing(Fields!Certification.Value), "", "Certs: " + Parameters!certs_string.Value) in the TextBox Expression.