| Author |
Message |
cofang
Newbie
Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
|
   Topic: formula to split string
Posted: 01 Mar 2007 at 9:15am |
I'm trying to write a formula to split a string. The string has 5 positions. When I use anything other than a 1 in the brackets to indicate the position, I get an error message saying, "A subscript must be between 1 and the size of the array.".
Is the offer for a free book still out there?
|
 IP Logged |
|
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 01 Mar 2007 at 11:32am |
|
What's your formula?
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
cofang
Newbie
Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
|
Posted: 01 Mar 2007 at 11:36am |
|
Split ({STU_TS_ELEMENT.STSE-SCORE-DATA}, " ")[2]
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 01 Mar 2007 at 11:55am |
|
The Crystal Reports Split() thinks that the array only has one element. I assume you're getting this when you preview the report? If so, then check all your data because you might have something that doesn't match the format. Also, I assume that you're splitting on the space character?
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
cofang
Newbie
Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
|
Posted: 01 Mar 2007 at 12:00pm |
Why does it think the array has only one element? Here's what the array looks like in the report, except that the separators are tall rectangles instead of the squares:
110244324Approach5Gen
The values I'm trying to extract or split into different columns are 10, 44, 2, Approach, Gen.
I've tried a semi-colon, a tab and a space in the formula to represent the separator, but that doesn't seem to make any difference.
If I have a numeral 1 in the brackets, I can preview the report but nothing changes in the array. When I have a numeral 2 in the brackets, I get the error message.
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 01 Mar 2007 at 12:23pm |
Now I see. The problem is that you can't enter a "tall rectangle" as the separator character because it's not on the keyboard. True? If so, you need to find out what the ASCII equivalant of that tall rectangle is and use it with the Chr() function. I'm guessing it's either the number 10 or 11 (carriage return or line feed). Once you find out ASCII number, use it in the following formula:
Split ({STU_TS_ELEMENT.STSE-SCORE-DATA}, Chr(ascii#))[4]
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
cofang
Newbie
Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
|
Posted: 01 Mar 2007 at 12:36pm |
I tried finding the ASCII number for the rectangle but can't find a rectangle on the look up table I found online. Can you direct me to another way to find what the rectangle stands for?
So I tried your formula with a 10 and then with an 11, but I still get the error about the subscript needing to be between 1 or the size of the array.
|
|
IP Logged |
|
cofang
Newbie
Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
|
Posted: 01 Mar 2007 at 12:41pm |
I think the ASCII equivalent is 49. I used the formula
Asc({STU_TS_ELEMENT.STSE-SCORE-DATA}) and it returned the value 49. So I tried that in the split formula and still got the subscript error message.
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 01 Mar 2007 at 12:45pm |
Well, you can use a dummy formula on your report just to see what the number is and then delete it afterwards. You know that there is a rectangle in the second position of the string. So use this formula to see what the ASCII equiv of the second character is.
AscW({STU_TS_ELEMENT.STSE-SCORE-DATA}[2])
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
cofang
Newbie
Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
|
Posted: 01 Mar 2007 at 12:50pm |
|
I sent you a reply before I got your response, so I've tried two different formulas to determine the ASCII character and got two different responses. I got the value of 21 when I used your formula. Again, when I used 21 as the separator in the split formula, I get the subscript error message.
|
|
IP Logged |
|
|
|
