Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

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!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

wprice (IS/IT--Management) (OP)
31 Mar 04 8:21
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 (TechnicalUser)
31 Mar 04 9:33
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 (IS/IT--Management) (OP)
31 Mar 04 15:55
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 (Programmer)
12 Nov 04 9:57
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 (TechnicalUser)
12 Nov 04 10:28
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 (Programmer)
12 Nov 04 10:39
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 (MIS)
12 Nov 04 12:13
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:

CODE

(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, www.microflo.com
Microflo provides expert consulting on MagicTSD and Crystal Reports.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close