Trim a Memo Field 1

[deepsheep]

I have what I hope is a simple problem. I'm using CR 8.5 and MSSQL 2005 and I have a large varchar field that CR interprets as a Memo.

My problem is that CR is interpreting some stuff (after the actual data) at the end of the field as Line breaks. Quite a few line breaks. I'd like to trim off or suppress the blank lines.

I just can't seem to find the right button.

Thanks!

 

[satinsilhouette]

Sounds like you have someone who slept on the space bar!

You will need an ascii character to look for to use to trim that field, because it is not really null or empty, it has some sort of character space holder. Otherwise trim would have worked.

Thanks so much!
satinsilhouette

 

[deepsheep]

I'm not sure how to do that, since CR 8.5 won't let me put a memo type into a formula.

Could you provide instructions or an example?

 

[lbass]

You could use a SQL expression (you will have to type the field name in). Try one of the following:

substr(table.`memo`,1 254)

Or

{fn substring(table.`memo`,1,254)}

...since there is a character limit in 8.5. You could then add additional SQL expressions, as in:

{fn substring(table.`memo`,255,254)}

The first number is the starting position, and the second is the length. In other words, you could select the beginning parts of the memo field, excluding the returns. I'm not sure whether there is the equivalent of a replace function in SQL expressions.

-LB

 

[deepsheep]

I've read about this before, but can't figure out where to put it. Do I just use it instead of a field name in a function or do I need to put it into the SQL query?

Thanks!

 

[lbass]

field explorer->SQL expression->new.

Punctuation and functions vary by datasource/connectivity, so you might want to check in database->show SQL query to see how you should punctuate the fields in the expression.

-LB

 

[deepsheep]

It was staring at me the whole time! Thanks!

I managed to hit another snag. Instead of taking 255 characters, the substring stops at the line breaks within the data. There will be many (and a variable number of) good line breaks in every report. The thought of needing to figure out the math and make 40+ of these expressions to make it work makes my brain hurt. Any suggestions?

If I use any function other than substring on that field (like ltrim & rtrim) I get "error in compiling sql expression" and "incorrect syntax near .". Other fields work fine, so I think the sytax is OK.

Thank you.

 

[lbass]

A line break is considered a character. I don't know how to replace these characters in a SQL expression--maybe someone else does. You could consider upgrading so you could use the memo field in a formula that uses the replace function.

-LB

 

[deepsheep]

I know through a stored procedure, the substring function doesn't care what character it is. I also know that other languages do care and will stop at a null or a line feed. So maybe its something in one of the drivers being 'helpful'.

I think I've got a work around anyway. I'm going to put a each record on a page, make the memo field the size of the page and stop it from growing. It usually takes up 1/2 a page anyway and almost never a whole page. Not the prettiest solution, but will work for this report.