stripping data out of a memo field in crystal report

[jenreb1]

my table, I have a memo field called "child_instructions". In the field's data there is the word SUTURE - I want to strip all the data from BEFORE the word SUTURE off and display the rest.

I tried using the Ltrim function but it doesn't work on the data type memo field .... anyone have an idea on how I can get this data to display ?

TIA.
Jenn

 

[chelseatech]

What version of CR are you using?

Memo fields can be used in a formula with version CR9 and later.

Use left({table.field},instr({table.field},"SUTURE"))
or
left({table.field},instr({table.field},"SUTURE")-6)
if you don't want to include that word

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[jenreb1]

I am suing 8.5 - maybe that is my problem, I get a syntax violation when I use that code ...

 

[devingm]

Hi jenreb1, I use 8.5 too, it's actually not that tricky. First you go down to "SQL Expressions Field" in the Field Explorer and click on "New" to create an expression. Let's name it "UnMemoChildInstructions" for this example. Don't worry, you don't really need to know any SQL to do this. Lets say your table is called JenrebsTable and your field is called "child_instructions". Now type in this expression:

{fn LEFT(JenrebsTable."child_instructions",250)}

Yes, those quotes are needed (it's a little different than Crystal formula syntax. What we have now is a SQL expression that contains the first 250 characters of the text in the memo field child_instructions. You can use UnMemoChildInstructions in place of the field name in your report or in any formulas now that you have created it.

So now you can use chelseatech's advice from above, only modified to this:

left(%UnMemoChildInstructions,instr(%UnMemoChildInstructions,"SUTURE"))

Hope that helps.