Help to replace text withing string field - CR version 7

[jannoth]

Hi all.

A text field contains the following string:
A=£64.00; B=£49.00; C=£10.00

I need to replace the A, B and C so that the text reads as follows:

Full=£64.00; Partial = £49.00; Remitted = £30.00

Can anyone please suggest a formula that would work in version 7?

Thanks

J

 

[CR85user]

Mmm - Nice approach LBass.

'J

 

[jannoth]

I've never seen such beauty in my life!!

THANK YOU - it worked perfectly

)

J

 

[jannoth]

Hi - I've just it a hurdle |

The formula was accepted by CR7 and passed my initial tests but this morning produces the following error:

"String length is less then 0 or not an integer"

I have confirmed that the database field containing "A=£64.00; B=£49.00; C=£10.00" is String with a lengh of 30 so definitely not integer.

Guessing that this might be down to empty fields, I added an IF THEN ELSE statement to only evaluate "If Not Isnull" but that made no difference.

Does anyone have any ideas what this might mean?

Thank you in advance.

J

 

[lbass]

Try changing the formula to this:

stringvar x := {table.string};

if instr(x,"A") > 0 and
instr(x,"B") > 0 and
instr(x,"C") > 0 then
(
"Full=" + mid(x,instr(x,"A")+2, instr(x,"B")-3-instr(x,"A")+1)+
"Partial=" + mid(x,instr(x,"B")+2, instr(x,"C")-3-instr(x,"B")+1)+
"Remitted=" + mid(x,instr(x,"C")+2)
) else
x

This should show you the entire field when it doesn't contain all elements (A,B,C).

-LB

 

[jannoth]

Yipee!

Thank you - no errors.

My only variance was to display the text "Please telephone us on 12345" instead of the whole field )

I'm still confused about what might cause the error cos null string fields would't have lengths of less than zero, would they?

Either way, than you sincerely Lbass.

 

[lbass]

Note the minuses in the formula. If instr(x,"B") for example = 0, then the formula would return a -3 minus the location of "A" for the second part, and it would be a negative number.

-LB

 

[jannoth]

Gottit - thanks )

J