How to truncate last 5 characters in a field ?

[richardm]

Hello everyone! What is the formula or command to truncate the last 5 characters of a field.

In the database I'm using the person doing data entry added ", PRN" to the end of a persons first name. Many of the first names look like:

Thomas, PRN
John, PRN
Suzy, PRN
Lisa, PRN

What is the formula to get rid of the ", PRN" in the {firstname.field} Any help will be greatly appreciated
Thank You.

 

[Naith]

If you're on Crystal 8+, you should be able to get away with Replace({firstname.field},', PRN','')

There are many other ways you can do this, so if this doesn't work out for you, come back.

Naith

 

[dgillz]

Left({fieldname},length({fieldname})-5) will work as well Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[richardm]

Thanks for your help. I must be doing something wrong becuase when I try the Replace command I get rid of all the first names and only "PRN" shows (which is the exact opposite of what I'm trying to do) and when I try the Left, Length command I get the message "string length is less than 0 or not an integer"

I appreciate your patience and help.

 

[Naith]

Bizarre indeed.

I'm assuming that "Thomas, PRN" is one field.

If it is, then as long as your replace syntax is exactly

({firstname.field},', PRN','')

then what you should end up with is "Thomas".

If that's not what you get, show me your replace syntax.

Naith

 

[Ngolem]

I has observed weird behavior using single quotes at times so I use double quotes exclusively now

I doubt that this works but try

replace({Table.name},", PRN",""}

also the comma in ", PRN" may be interfering with Crystals interpretation of the formula

perhaps using the CHR equivalent chr(44) might be better

replace({Table.name},chr(44) +" PRN",""}

but the revealation of Left not working is weird too


AHHHH is there NULLS IN YOUR DATA???

try this

WhilePrintingRecords;

if not isnull ({Table.name}) then
replace({Table.name},chr(44) +" PRN",""}
else
" ";

Jim Broadbent

 

[richardm]

Thank you very much for your help. The replace command worked!!!

My problem was that there was an inconsistency in the way the data was entered. I thought ", PRN" had only been added to the {firstname.field}, turns out it had also been randomly added to the {lastname.field}. I had to use the replace command for both.

Thanks again.