#Error when exporting to .csv from ssrs when trying to replace a comma with another field-CodePudding

I am trying to replace a comma with another field so you can import the data using a csv. I can do that successfuly by adding .ToString().Replace(","," And ") to the end of the field EX. Fields!Fieldname.Value.ToString().Replace(","," And ")). This will replace the comma with the word And. The issue I have encounterd is when the field is blank. It then exports a #Error in the cell. I have tried hiding the cell if it is blank but that works for everything but when it exports to a .csv.

If I cannot get the field to be just blank I would like to have it display 99.

Please help.

I have tried this as well... =Iif(isNothing(Fields!FieldName.Value),"99",(Fields!FieldName.Value.ToString().Replace(","," And ")))

It still displayed an error

CodePudding user response：

The problem is that you are trying to convert NULL (Nothing) to a string. Both sides of IIF are evaluated so even though you will never see the result, false part is failing when FieldName is null.

TO get round this, we first check if the field is null, then replace that with and emptry string "" and then do the ToString() bit.

try the following...

=IIF(
    isNothing(Fields!TestField.Value),
    "",
    (IIF(IsNothing(Fields!TestField.Value), 
         "", 
         Fields!TestField.Value
        ).ToString().Replace(","," And "))
    )