CODEWITHSUNDEEP
crystal-reports
Charles
Charles

Reputation: 93

Split: A subscript must be between 1 and the size of the array

I have a super simple formula. The problem is that sometimes the data doesn't have a second value, or sometimes the value is blank. 

Split ({PO_RECEIVE.VENDOR_LOT_ID}," ")[2]

ID
111 222
123

123 222

I was thinking if I could come up with some logic to figure out whether the string has multiple value's it would solve my problem, but haven't quiet found what I'm looking for:

If {PO_RECEIVE.VENDOR_LOT_ID} = SingleOrBlankString then 
{PO_RECEIVE.VENDOR_LOT_ID} else
Split ({PO_RECEIVE.VENDOR_LOT_ID}," ")[2]

Better Example Data:

3011111*42011111111
2711 00291111111

711111//12111111111
/J1111 69111111111
170111

Upvotes: 1

Views: 1390

Answers (1)

MatSnow
MatSnow

Reputation: 7537

If the string can contain a maximum of two values, separated by a space, then you can check if the string contains a space using the InStr function:

If InStr({PO_RECEIVE.VENDOR_LOT_ID}, " ") > 0 Then
    {PO_RECEIVE.VENDOR_LOT_ID}
Else
    Split ({PO_RECEIVE.VENDOR_LOT_ID}," ")[2]

If there can be multiple spaces between the parts you can use following formulas to get the values:

Left part:

This function returns the left part of the string until the first space.

If InStr({PO_RECEIVE.VENDOR_LOT_ID}, " ") > 0 Then
    Left({PO_RECEIVE.VENDOR_LOT_ID}, InStr({PO_RECEIVE.VENDOR_LOT_ID}, " "))

Right part:

This function returns the right part of the string after the last space.

  • The InStrRev-function returns the position of the last space because it searches the string backwards.
  • The Len-function returns the length of the string.
  • [length] - [position of last space] = [length of the right part]


If InStr({PO_RECEIVE.VENDOR_LOT_ID}, " ") > 0 Then
    Right({PO_RECEIVE.VENDOR_LOT_ID}, Len({PO_RECEIVE.VENDOR_LOT_ID}) - InStrRev(testString, " "))

Upvotes: 1

Related Questions