Pulling last name out of string

[mwhalen]

Any idea how to do this? In our database, our name table has a name field, ex. "Smith,John". The data entry people are supposed to enter in that format - lastname followed by comma followed by firstname. I'm trying to pull out surname out of that field. So we use the following formula



if {PS_Y_DONOR.SUBTYPE}='I' then
Left ({PS_Y_NAME_TBL.CUSTOMERNAME}, InStr ({PS_Y_NAME_TBL.CUSTOMERNAME},",&quot-1)
else if {PS_Y_DONOR.SUBTYPE}='O' then
{PS_Y_NAME_TBL.CUSTOMERNAME}

The letter I represents individual and the letter O represents Organization. The problem we run into is that what if the records person failed to enter a comma? It's failing if there is no comma in there so I'm not sure how to rewrite the formula. Also we are using version 7

 

[SmpTraining]

Try something like:

pos := instr({PS_Y_NAME_TBL.CUSTOMERNAME}, ",");
if pos=0 then 
   pos := instr({PS_Y_NAME_TBL.CUSTOMERNAME}, " ");

Right( {PS_Y_NAME_TBL.CUSTOMERNAME} , pos )

I'm afraid I'm not at a machine right now to test this so you may get the odd syntax error. I think it's right though.

Steve Phillips, Crystal Consultant

 

[dgillz]

There was a similar post to this about a month ago. If you do a search you may find it. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[dgillz]

I actually saved this from the last post. Heres the solution:

Mid({Stringfield},instrrev({Stringfield}," &quot+1)

Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[chelseatech]

But what about somebody with a double word surname - there are many examples of these from Dino de Laurentis to Vincent van Gogh.... And how do you handle someone with two first names (Billy Bob Thornton)? Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[FoxG]

Hi,

There is no way to cater for typing errors.
The only real solution is to data cleanse your fields so that they are in the correct format!

The only formats that will work are...
a) 3 distinct fields for forename, midname and surname
b) surname,forename midname(s)

Both have problems if no middle name is supplied, as you then need to test for null values in your formula to prevent records being ignored.

Hth,
Geoff