Search code examples
reporting-servicesssrs-2008

Reporting services expression #Error with "mid"

I have a problem with a expression in a reporting services 2012. The expression is:

iif(IsNothing(Fields!CHEGADA_LUME.Value)," ",mid(Fields!CHEGADA_LUME.Value,12,instr(Fields!CHEGADA_LUME.Value,":00")-12))

When, the value is null (first condition is true), the expression returns "#Error". However, if the value is not null, the expression works good.

Solution

  • Iif evaluates both expressions not matter which one returns.

    Mid cannot accept null as value (it expects integer), so you have to return 0 as the third parameter of mid when your string is null, making an extra check.

    =iif(
IsNothing(Fields!CHEGADA_LUME.Value),
" ",
mid(Fields!CHEGADA_LUME.Value,12,iif(
IsNothing(Fields!CHEGADA_LUME.Value),0,instr(Fields!CHEGADA_LUME.Value,":00")-12)
))