Trimming around carriage return 2

[tonyvee1973]

Hi Guys
I have a field which hold 2 or 3 lines of address. I need to get each line in its own field in crystal.
For the top line i have used:
replace({mytable.myfield},chr(40)," ")
and this works ok

For the next line i tried the formula below to show all after the carriage return but it goes wrong:
if instr({mytable.myfield}, chr(13)) > 1
then Right({mytable.myfield},instr({mytable.myfield}, chr(13)))

Surely there myust be a simpler way?

 

[CoSpringsGuy]

try this

formula 1
if instr({mytable.myfield},chr(13)) > 0 then
split({mytable.myfield},chr(13))[1]
else
{mytable.myfield}

formula 2
if instr({mytable.myfield},chr(13)) > 0 then
split({mytable.myfield},chr(13))[2]
else
""

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[tonyvee1973]

Thanks Cospringguy - worked like a charm. Can you explain the logic for me please so i get it?
Also how would i work it for a third line?
Thanks

 

[MCuthill]

Hope I don't step on your toes CoSpringsGuy, but I think I understand your solution (awesome, by the way - I think I follow it, and not sure how I hadn't pondered it before).

TonyVee: Basically, Split() creates and array, with elements divided by the specified character (a carriage return in this case) and then returns the sought element in the array. Assuming I understand CoSpringsGuy's solution, to return a third line should look like:

formula 3
if instr({mytable.myfield},chr(13)) > 0 then
split({mytable.myfield},chr(13))[[red]3[/red]]
else
""

The number in red is the element number in the array. My sincerest apologies if I misinterpretted the solution and muddled the waters on ya (I think we are safe though haha).

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[kray4660]

First off, what happens? From what I see, it will it not work properly. The Right({mytable.myfield},instr({mytable.myfield}, chr(13))) should be changed to Right({mytable.myfield},len({mytable.myfield}) -instr({mytable.myfield}, chr(13))). Remember the Right function is the String, and the number of characters to return.

I hope this helps.

 

[CoSpringsGuy]

My only correction would be for formula three.. If there is only 2 chr(13) in the original string, this will throw an error
so do this

formula 3
if UBOUND(split({mytable.myfield},chr(13))) > 2 then
split({mytable.myfield},chr(13))[3]
else
""

I haven't tested that but it should work

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection