Moving last names to beginning of field

[SApple]

I know this question must come up often, but I can't seem to locate the answer. I'm working in CRD v.7.0, and bringing in a field that has first name/middle initial/last name and want to format it last name first. I get this done by the following formula (called @UpperOriginator):

Uppercase(Trim({P.Originator}
[Instr({P.Originator}," &quot+1 to 25])+", " +
Trim({P.Originator}
[1 to Instr({P.Originator}," &quot-1]))

where P.Originator is the field.

This works fine except for some names which have middle initials (less than 25%). Then they are showing up as:

Z. Doe, John

I wrote the following additional formula:

if "*. " in ({@UpperOriginator}) then
mid({@UpperOriginator},4)
else {@UpperOriginator}

but this doesn't seem to work. Any ideas, gang? Thanks!

 

[kenhamady]

Try:
if ". " in ({@UpperOriginator}) then
{@UpperOriginator} [4 to length({@UpperOriginator})]
else {@UpperOriginator}

You don't need the "*" with IN and
you didn't give the third argument to MID. I prefer the subscript to MID myself. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[kenhamady]

Actually, I just realized that if you don't give MID the third argument, it goes to the end. Didn't know that.

So your only error was including the * in the condition. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[SApple]

Thanks as always, Ken. You are good. I took out the wildcard (of course now I realize I didn't need it!) and the names are coming in terrific.

Now if I want to keep middle initials in on other reports (for names distinguished only by middle initials) do you have a suggestion as to how best to do that?

I appreciate your help. Thanks!

 

[kenhamady]

Can you add 4 chars to the first name length when there is a ". "? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[SApple]

Ken, (1) which formula would you modify and how; and (2) now I'm told there are other names with first initial/full middle name/last name. <sigh> Thinking I should have left well-enough alone...

I'm just not sure how to accommodate all the various permutations of folks' names...?

 

[kenhamady]

You would have to change the first formula, but I haven't waded through it to figure out how.

I think the permutations are getting me beyond what I can do here. Sounds like you may need a long nested if-then statement. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[dcm]

SApple: Try this for size:

numbervar start_of_surname:=InstrRev({P.Originator},' ')+1;

// I think this function is downloadable from seagate
//web site at

http://www.seagatesoftware.com.

If not then
//same effect can be obtained but with more complication

numbervar end_of_firstname:=Instr({P.Originator},' ')-1;


if {?sort_order_param} = 'Lastname, Firstname' then
mid({P.Originator},start_of_surname)+&quot;, &quot;+left({P.Originator},end_of_firstname)
else
if ({?sort_order_param} = 'Lastname, Fullnames' then
mid({P.Originator},start_of_surname)+&quot;, &quot;+left({P.Originator},start_of_surname-1)
else
if ({?sort_order_param} = 'Lastname, 2 initials' then
mid({P.Originator},start_of_surname)+&quot;, &quot;+left({P.Originator},1)+&quot;. &quot;+mid({P.Originator},end_of_firstname+1,1)+&quot;.&quot;

hth


David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner