Last character trimmedoff in Crystal Reports 1

[karnd]

Hi,

I have a address field and the data stored as "450,Kissena Blvd,Flushing,NY-" so i have tried to remove the last character in that string which ends with "-" but does not helped much with Replace function in crystal reports.

Note:Any character ends with - or , has to be removed.

Appreciates of your ideas.

Thanks a lot,
Regareds

 

[IdoMillet]

IF {address}[Length({address}] = "-" or {address}[Length({address}] = "," THEN
LEFT({address}, Length({address} - 1) 
ELSE
{address}

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[karnd]

Hi IdoMillet,

The above syntax did not allow me to get the results and it is asking for the number supplied for the LEFT function in that syntax. Could you please try from yourside and i am very new to Crystal Reports.
Crystal error message: a number is required here and stops at LEFT function.


I would appreciates of your cooperation.

Thanks a lot,
Regards,

 

[TheBlondOne]

You could try this

if instr({address},"-") <> 0 or instr({Address},",") <> 0 then left({address},length({address}) - 1) else
{address}

HTH

Steve

 

[chaverly]

This works great, but what is it doing? I have never seen this before and would like it broken down. I understand the Left() part, but not the {address}length({address)) part.

 

[IdoMillet]

You can refer to any character inside a string by its position using the syntax:

{some_string}[i]

Where i is the position of the character within the string.

By using the Length() function, you can specify the position as the last one in the string.

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[TheBlondOne]

Just to reiterate on IdoMillet's comment he's absoloutly right in the way that its working, maybe a slightly more simpler way is that in the first part of the formula code - the instr part - what your checking for is either the character "," or "-", what crystal will do is search the string field to see if it contains either of these characters, if it does it will return a number other than 0 hence if instr({address},"-") <> . What you can then do then is in the next statement which starts left(etc...) is tell crystal to show all of the text string except for the last character which will be "," or "-". Normally you would do left({Field},3) for example but because the field is not always a set length this wouldn't work so instead if you don't know how big a field is you can use the length()function and this will return the number of characters that the field contains, so it could be 20 on one field but 70 on another, and if the field does contain a "," or a "-" then you can just take the last charater off the string hence the - 1 part.

HTH

Steve

 

[IdoMillet]

Steve,

The problem with your approach is that it would trigger a chopping action even if "-" or ";" are in the middle of the string.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[TheBlondOne]

Thanks for the feedback IdoMillet, your right it would force a trim if "-" or ";" are anywhere in the string.

Thanks

Steve