#Error in Paginated Report expression, using mid and instr

[KeithLowry]

Hi there,

I'm trying to strip some font formatting from a comments field, but leave other HTML formatting in place for a Report Builder report. I have written this expression in Report Builder as part of the testing process:

=iif(InStr(1,Fields!Body__c.Value,"<span")=0,Fields!Body.Value,
Mid(Fields!Body.Value,

InStr(1,Fields!Body.Value,"<span"),

InStr(InStr(1,Fields!Body.Value,"<span")+1,Fields!Body.Value, ">")-
InStr(1,Fields!Body.Value,"<span")+1)
)

the result brings me back the full string of the <span ... > characters I'm looking for, which is great, but if the <span string is not present the formula returns #Error in the report. 

If I simplify the IIF to just return "no" or "yes" depending on the inital InStr search then the statement works ok - why does the #Error appear when trying to do the whole thing (and for the relatively simple part of the expression - the hard part works!)?

As an aside how to others here test their expressions - Report Builder has given no indication of why the #Error is thrown so troubleshooting is really hard. I'm coming from Crystal Reports where there would be some kind of error message to explain the error at least.

thanks

Keith

[KeithLowry · Accepted solution]

in the end I had to create a second formula which checked the length of the above search/Mid expression, and output the search expression if it was greater than 0 and the original Body text if it wasn't. IsError didn't want to work for smoe reason.