Print Page | Close Window

formula to split string

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=263
Printed Date: 01 May 2024 at 4:25am


Topic: formula to split string
Posted By: cofang
Subject: formula to split string
Date 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?


Replies:
Posted By: BrianBischof
Date 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>

Posted By: cofang
Date Posted: 01 Mar 2007 at 11:36am
Split ({STU_TS_ELEMENT.STSE-SCORE-DATA}, " ")[2]

Posted By: BrianBischof
Date 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>

Posted By: cofang
Date 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.

Posted By: BrianBischof
Date 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>

Posted By: cofang
Date 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.

Posted By: cofang
Date 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.

Posted By: BrianBischof
Date 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>

Posted By: cofang
Date 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.


Print Page | Close Window