Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: formula to split string Post Reply Post New Topic
Author Message
cofang
Newbie
Newbie


Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
Quote cofang Replybullet 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 IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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 IP Logged
cofang
Newbie
Newbie


Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
Quote cofang Replybullet Posted: 01 Mar 2007 at 11:36am
Split ({STU_TS_ELEMENT.STSE-SCORE-DATA}, " ")[2]
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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 IP Logged
cofang
Newbie
Newbie


Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
Quote cofang Replybullet 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 IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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 IP Logged
cofang
Newbie
Newbie


Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
Quote cofang Replybullet 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 IP Logged
cofang
Newbie
Newbie


Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
Quote cofang Replybullet 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 IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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 IP Logged
cofang
Newbie
Newbie


Joined: 01 Mar 2007
Location: United States
Online Status: Offline
Posts: 6
Quote cofang Replybullet 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 IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.