FAO Lbass - Removing Chr(13)+Chr(10) from a field 1

[munchen]

Lbass

A few weeks ago you posted the following solution to remove chr(13) + chr(10) from a field. This formula seemed to be working perfectly however occasionally the address database field can be a null and I get the following error message:

"String length is less than 0 or not an integer".

Do I do add a strlength or a isnull option to the following piece of code?

Hope you can help.

whileprintingrecords;
numbervar counter := 0;
stringvar orig := {tblAuthorisations.Address};
stringvar norets := replace({tblAuthorisations.Address},chr(13),chr(10));
stringvar noretsorlinefeeds := replace(norets,chr(10),"");
numbervar i := len(orig) - len(noretsorlinefeeds);
stringvar result := "";
numbervar counterx := 0;

for counter := 1 to i do(
if ucase(left(split(norets,chr(10))[counter],1)) in ["A" to "Z","1" to "9"] then
counterx := counterx + 1);
left(orig, len(orig) - (i - counterx + 1));

 

[munchen]

Think i managed to get it working myself by altering the 3rd line of the code like so:

stringvar orig:=iif(len({tblAuthorisations.Address})= 0 ," ",{tblAuthorisations.Address});

Is there a str length limit on formulas in crystal as the else statement below displays all the address information but the 1st line seems to cut off the last few letters from the address field?

{@NewAddress} + chr(13) + {tblAuthorisations.Postcode}
else
{tblAuthorisations.Address} + chr(13) + {tblAuthorisations.Postcode}

Example (Line 1)

Fernbank
Dores
Inverness
Inverness S
IV26TR

Example (Else Statement)

Fernbank
Dores
Inverness
Inverness Shire
IV26TR

 

[lbass]

I think this is a result of variation in your field in conjunction with the formula. Can you provide the contents of the field {tblAuthorisations.Address} for the particular instance that is causing the problem?

-LB

 

[munchen]

lbass

The contents of the field {tblAuthorisations.Address} for the particular instance that is causing the problem is:

Fernbank
Dores
Inverness
Inverness Shire

When I display the {@NewAddress} formula field that you gave me it displays:

Fernbank
Dores
Inverness
Inverness S

and cuts off the last four letters of the word Shire. However if i display the {tblAuthorisations.Address} field then all letters are displayed correctly, but i do need to display the {@NewAddress} formula field as this removes all the chr(13)+chr(10) from this field.

Hope this is what you needed.

 

[lbass]

Change the last line of the formula to:

left(orig, len(orig) - (i/counterx - counterx + 1));

Also, you can remove the "stringvar result := "";" from the variable declarations, as this was a holdover from when I was trying to develop the formula, but I never used it...

-LB

 

[munchen]

lbass

I changed the last line like you suggested but it immediately came up with a division by zero error.

left(orig, len(orig) - (i/counterx - counterx + 1));

 

[Madawc]

Test for zero before trying to divide.

Madawc Williams (East Anglia, UK)

 

[lbass]

Not sure, but try:

If orig <> "" then
left(orig, len(orig) - (i/counterx - counterx + 1));

-LB

 

[munchen]

Worked perfectly lbass many thanks.

Thanks also to madawc, i knew i had to test for the zero but i wasn't exactly sure how to go about it.