Substring Error in Report Designer

Hi ​@jbuddecke71,

Could you please try the formula below and let me know if it works for you?

=IIF(
    IsNull([ARTran.TranDesc],'')='',
    '',
    IIF(
        InStr([ARTran.TranDesc], '|') <= 0,
        [ARTran.TranDesc],
        Substring([ARTran.TranDesc], 0, InStr([ARTran.TranDesc], '|') - 1)
    )
)

This formula should return the entire description if the | character is not present. If the | character exists, it will return only the text before the first occurrence of |. It also handles null or empty values to prevent errors.

Please let me know the results or if you encounter any issues.

​@jbuddecke71 As Dipak pointed out your function needs to include a clause of what to do when the | character isn’t present. This is where IIF() functions come into play.

​@Dipak Nilkanth Thanks for the response but I got a similar error.

An error occurred while executing the Substring(Identifier(ARTran.TranDesc), Const(0), BinaryOp - (InStr(Identifier(ARTran.TranDesc), Const(|)), Const(1))) function. Length cannot be less than zero

Hi ​@jbuddecke71,
Use Below formula, I tested in local environment and its worked fine for me.

​​​​​​​=Substring(
    [ARTran.TranDesc],
    0,
    IIF(InStr([ARTran.TranDesc], '|') <= 0,
        Len([ARTran.TranDesc]),
        InStr([ARTran.TranDesc], '|') - 1)
)

to debug this, Add a temporary text box with:

=InStr([ARTran.TranDesc], '|')

and preview the report. If you see values of 0, then the issue is definitely that Substring() is still being evaluated with a negative length.

​@Dipak Nilkanth 

Using your code threw an error.

The debugging code returned the following.

​@Abhishek Niikam Thanks for your help on this!!! That seems to work.