INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...Many thanks to you for putting it together and to the forum members for taking the time to post their replies and give their time to help others. Their isn't another site that can touch it..."
Geography
Where in the world do Tek-Tips members come from?
|
Splitting Text (2)
|
|
I have a memo field that contains multiple lines of text that I need to split. I understand the "string" [3 to 5] method but should I use stringvar to pull multiple lines? Here's a sample of the memo field:
Date: 20120509
Location: New York, NY
Institution: Wells Fargo
I need to be able to extract the values for each line.
Thanks!!! |
|
|
Charliy (Programmer) |
9 May 12 10:03 |
If it's already breaking into lines like that when you print it, then you can look for chr(10) or chr(13) as breakpoints. |
|
But if I want to capture "New York, NY" how do I tell it to trim at the end of the row that starts with "Location:"? |
|
Are date: Location: and Intstitution: the only three items always found in the memo field and are they always there? Do you need these returned as seperate fields? _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
Yes, the field labels will always be the same label and length, but the position may not be the same because there's a description field before those fields. For example:
Row_ID: 10456
Description: This is just a test of the field
Date: 20120509
Location: New York, NY
Institution: Wells Fargo
Row_ID: 10457
Description: This is just a test of the information that was previously archived but has been restored.
Date: 20120510
Location: Miami, FL
Institution: Bank of America
|
|
but you only want those three items removed (without labels) from the memo field? and you do want them as seperate fields? _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
That's exactly what I need. |
|
k you will need to find out if the break is a chr(10) or chr(13) so try this: mDate formula local stringvar array a := split({yourmemofield},chr(13)); local stringvar b; b := replace(a[3],"Date: ",""); local datevar mDate := date( tonumber(mid(b,1,4)), tonumber(mid(b,5,2)), tonumber(mid(b,7,2)) ); i took the liberty of converting the date to an actual date field as well. If that doesnt work change the CHR(13) in the split function to chr(10) once that works for you mLocation formula local stringvar array a := split({@test},chr(13)); local stringvar mLocation := replace(a[4],"Location: ",""); mInstitition formula local stringvar array a := split({@test},chr(13)); local stringvar mInstitution := replace(a[5],"Institution: ",""); _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
change {@test} in the last two formulas to your memfield from database... _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
The formula is okay, but when I preview, I'm getting the following error:
"A subscript must be between 1 and the size of the array." |
|
CoSprings, I need to update my example. It looked right in the window, but not in the post:
Row_ID: 10456
Description: This is just a test of the field
Date: 20120509
Location: New York, NY
Institution: Wells Fargo
Row_ID: 10457
Description: This is just a test of the information that was
previously archived but has been restored.
Date: 20120510
Location: Miami, FL
Institution: Bank of America
Notice how the multi-line Description changes the row of Date, Location, etc.
|
|
ahhh ok that sucks! lol .. ok i am in a meeting but we can still fix that.. I will get back to you .. did you determine if it was chr(10) or chr(13)? _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
date formula - let me know how that works for you and I will do the rest .. boring meeting but I have to pretend Im paying attention local stringvar a := replace({youmemofield},chr(13),""); local stringvar b := mid(a,instr(a,"Date:")+6,8); local datevar mDate := date( tonumber(mid (b,1,4)), tonumber(mid (b,5,2)), tonumber(mid (b,7,2)) ); mDate _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
Location formula local stringvar a := replace({yourfield},chr(13),""); local numbervar b := instr(a,"Institution:"); local numbervar c := instr(a,"Location:")+10; local stringvar mLocation := mid(a,c,(b-c)); mLocation Institution Formula local stringvar a := replace({yourfield},chr(13),""); local numbervar b := instr(a,"Institution:")+13; local stringvar mLocation := mid(a,b); mLocation _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
They work, but they're also pulling everything after the desired field. For example, @Location returns:
Miami, FL
Institution: Bank of America
|
|
did you copy my formula or retype. The only way I see that happening is if you didnt spell Institution in the line "local numbervar b := instr(a,"Institution:");" exactly as it would appear in the memo field _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
I copied and changed the fields, but I'm still testing. |
|
|
lbass (TechnicalUser) |
9 May 12 21:47 |
stringvar p := {table.memo};
stringvar array q := split(p,chr(13));
mid(q[4],instr(q[4],": ")+2); //4th line
-LB |
|
LB - I like yours better .. I had initially started using split but my mind went a different direction and it worked so i stopped.. _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
Thanks lbass, but what if the line number is moved by the text in the memo field? Say instead of the 4th line, now the "Location" is on the 7th line? |
|
lbass, I also received the error when I used the formula: "A subscript must be between 1 and the size of the array." |
|
thats because of the unknown amount of chr(13) encountered if the description is several lines long. Thats why I just stripped those characters (chr(13)) and searched for the field names you were looking for. Is it still working correctly? _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
Of course not, lol. I tried the other formula and I got the subscript error, but when I switched back, I still get the error. Here's what I'm using: local stringvar a := replace({table.memo},chr(13),"");
local numbervar b := instr(a,"Institution:");
local numbervar c := instr(a,"Location:")+14;
local stringvar mLocation := mid(a,c,(b-c));
mLocation
|
|
Of course not, lol. I tried the other formula and I got the subscript error, but when I switched back, I still get the error. Here's what I'm using: local stringvar a := replace({table.memo},chr(13),"");
local numbervar b := instr(a,"Institution:");
local numbervar c := instr(a,"Location:")+14;
local stringvar mLocation := mid(a,c,(b-c));
mLocation
|
|
so whats the error that you get with that code? I noticed you added 14 instead of 10 on the third line. By doing this you will cut off the first 4 digits of your city I believe. is the error "String length less than zero or not an integer?" _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
Yes, that's the error, the subscript error was from earlier. I changed the 14 back to 10. |
|
local stringvar a := replace({table.memo},chr(13),"");
local numbervar b := instr(a,"Institution:");
local numbervar c := instr(a,"Location:")+10;
local stringvar mLocation ;
if b>c then
MLocation := mid(a,c,(b-c));
mLocation
_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
|
lbass (TechnicalUser) |
10 May 12 22:19 |
stringvar p := {table.memo};
stringvar array q := split(p,chr(13));
if ubound(q)>=4 then
mid(q[4],instr(q[4],": ")+2); //4th line -LB |
|
Great news! I got both to work flawlessly!! Thanks for all your assistance guys!! |
|
And I thought I was done..... Maybe I should start a new thread, but I have a Notes section, at the bottom of the memo field, that is being trimmed on the second row as if there's a character limit on the field. The entire entry is displayed in the database, but not in the report. Any ideas? |
|
|
andymc (Vendor) |
11 May 12 15:16 |
You might try right-clicking, Format, the field and on the Main tab, click Can Grow. |
|
Thanks andymc, "can grow" is already checked. |
|
in field explorer, right click and select show field type... see how many characters it shows and see if that corresponds with your database field _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
It just says "memo" field. |
|
"Maybe I should start a new thread, but I have a Notes section, at the bottom of the memo field, that is being trimmed on the second row as if there's a character limit on the field. The entire entry is displayed in the database, but not in the report. Any ideas? " what do you mean by "at the bottom of the memo field"? _____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection
|
|
Here are the rows. In addition to a few other lines in the memo field, there's a "Notes" line, as well. This section is the one that's being cut off at the same position, even before I use your formula to split the text.
Row_ID: 10456
Description: This is just a test of the field
Date: 20120509
Location: New York, NY
Institution: Wells Fargo
Notes:TestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestData
Row_ID: 10457
Description: This is just a test of the information that was previously archived but has been restored.
Date: 20120510
Location: Miami, FL
Institution: Bank of America
Notes:TestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestDataTestData |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One