Remove line breaks in field

[wprice]

I have a comment (memo) field in a report I am exporting to a tab delimited txt file. In this field there will frequently be line breaks / hard returns that show up on separate lines in the detail section of my report. I need to eliminate these so all lines in the comment field show up on one line. i.e., instead of :

text
text
text

I want:

text text text

Is there a formula to ignore or remove these line breaks/hard returns?

Thanks for your help

 

[lbass]

You need to determine what is causing the line breaks, but you might try:

replace(replace({table.string}, chr(13)," "), chr(10)," ")

I think either character could cause a return.

-LB

 

[wprice]

The returns are done in the application, a purchase order software.
Since this is a memo field i won't be able to do a formula. I'm using CR 8.5 , to an SQL 2000 database. I tried a conversion SQL expression I found in a search of the forums but couldn't get them to work.
I can export to Excel and it will keep everything in that field on one line. It just an extra step I'd rather not take.
Thanks anyway
BP

 

[pelajhia]

I am having the same problem. I am grabbing data from a peoplesoft db, Oracle 8i, CR10.
The fields shows as memo in crystal, the chr(13) replace doesn't work but I can't tell if it's because the return is some other special char or the formula won't work on a memo field.
Any advice would be greatly appreciated.
Thanks!

 

[mbarron]

Since I'm only using version 8, I'm not sure how V10 handles memo fields. You may be able to adapt the following to give you a list of the chr() values of the members of the field. You would then just have count the characters to find the "offending" chr value.

stringvar test:="your memo field here" ;
numbervar loopit:=len(test);
stringvar output;
numbervar looper;

for looper:=1 to loopit do(
output:=output & totext(asc(test [looper]),0) &"," ) ;
output


Mike

 

[pelajhia]

Ok, I did some testing by using instr({myfield},'A') to find out if the formula would likely work on the memofield in cr10, and it does.
Then to figure out what special char was being used, I created a quick report that takes in a bunch of records from a table (any table will do). In the detail line I used the following formula - chr(recordnumber) to display what each char number would show. This showed that, for the Arial font, there are several 'blank' chars in addition to chr(13), including 266 and 269, which ended up being the value I was looking to eliminate.
Not fool proof, because I think the fonts vary in this respect, however a good basic font should give some clue as to what chars are "special". Thanks for the quick response, mbarron.

 

[rosemaryl]

Dear Wprice,

The issue with our sql expression is that in 8.5 SQL Server ODBC the return of the sql expression is limited to 254 total characters.

Does the data stored in this memo field, exceed that length?

If not, then this will work:

(Substring(
Replace(
    Replace(
        Substring(Incident."Incident Description",1,8000)
    ,char(13),' ')
,char(10),' ')
 ,1,254))

I lined it up the way I did, so that you could see the values that goes with each function. Replace Incident."Incident Description" with the name of your memo field, it will not show up in the list of available fields, you must enter it yourself.

If you need more than 254 characters, at CR 8.5 version, then your only option is to create a field in the view you are reporting on that does the same thing, but returns all the data in the field after stripping the carriage return and line field characters.

Regards,
ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.