Removing chr(13) + chr(10) from a field 1

[munchen]

I am using crystal reports 8.5 and i am connecting using an ODBC connection to a Microsoft Access database.

I have a formula field that is concatenating the address and postcode fields together with a chr(13) between them.
{tblAuth.Address}+chr(13)+{tblAuth.Postcode}

The problem is that the address field in the access database sometimes has chr(13) or chr(10) after the last line of this field and so my formula field is displayed like this:

10 New Road
Islington
London


NW7 6TY

Is there any way I can remove the chr(13) and chr(10) after the last line only?

I know using the replace function that I can remove all occurrences of chr(13) and chr(10)

Replace(Replace({tblAuth.Address},chr(13),""),chr(10),"")

However i only want to remove the ones after London so the address will be displayed like this:
10 New Road
Islington
London
NW7 6TY

Hope someone can help.

 

[Madawc]

Do a formula field:

if right({tblAuth.Address}, 1) = chr(13)
or right({tblAuth.Address}, 1) = chr(10)
then left({tblAuth.Address}, length({tblAuth.Address}-1)
else
{tblAuth.Address}

That's assuming that the skip character is the last character. Failing that, you'd need to use TrimRight to make it so.

Madawc Williams (East Anglia, UK)

 

[munchen]

Madawc

I keep getting an error when I try your code saying there is a closing bracket missing on the following line:

then left({tblAuth.Address}, length({tblAuth.Address}-1)

Regarding your comment about using the trim right function how would TrimRight({tblAuth.Address}) remove the chr(13) or chr(10)? I thought this function just removed trailing blanks.

 

[lbass]

Is the entire three-line address part of {tblAuth.Address}?
If so, I think you could do the following:

if right({tbl.Auth.Address},1) in [chr(10),chr(13)] then
{tbl.Auth.Address}+{tblAuth.Postcode} else
{tblAuth.Address}+chr(13)+{tblAuth.Postcode}

If you want to try Madawc's approach, try:

if right({tblAuth.Address}, 1) = chr(13)
or right({tblAuth.Address}, 1) = chr(10)
then left({tblAuth.Address}, length({tblAuth.Address})-1)
else
{tblAuth.Address}

-LB

 

[munchen]

My apologies I forgot to say that there could be more than one chr(13) or chr(10). I tried the formula from lbass and it did indeed remove one carriage return but now I assume I will have to do some sort of loop to check there are no further chr(10) or chr(13) at the end of the address field.

The entire 3/4 line address is part of {tblAuthorisations.Address}.

This is what I have attempted so far but I keep getting the error - A loop was evaluated more than the maximum number of times allowed.

stringvar svar := {tblAuthorisations.Address};

do
(
svar = iif (right(svar,1)in[chr(13),chr(10)], left(svar,len(svar) -1), svar);
)
while right(svar,1)in[chr(13),chr(10)];

svar + chr(13)+{tblAuthorisations.Postcode}

 

[lbass]

There might be a simpler formula for this, but the following seems to work, when I test it here:

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));

Then you could concatenate this formula with a return and the postal code field.

-LB

 

[munchen]

Thanks lbass

This worked and i just concatenated this formula with a chr(13) and the postcode to get the finished result.

Many thanks