How do I split a string in Crystal Reports 7?

ferguson_jerald
ferguson_jerald used Ask the Experts™
on
Hello Experts.

I have a Crystal Report that has a field with a persons entire name.  Some people have "firstName middleInitial lastName", "firstName lastName", "firstName1 firstName2 middleName1 middleName2 lastName".

The entries all vary in length, but all I need is the last name.  The last name is always preceded by a space.  How do I split the entry and capture just that part of the string following the last space?

Any help is greatly appreciated.

Thanks,
J
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try this.  It assumes there is always a lastname.  This will just display the name

Local NumberVar LastNameBegin;
Global StringVar LastName;
If IsNull({NameFIeld} then
   LastName := ""
Else
(
    LastNameBegin := InStrRev({NameFIeld})," ");
    LastName := Mid({NameFIeld},LastNameBegin+1);
);
LastName

mlmcc

Author

Commented:
Thank you for the quick response.  I am getting the following error:

"A number, currency amount, boolean or string is expected here."
The cursor is positioned immediately in front of InStrRev .  Any ideas on why I'm getting this error?

Local NumberVar LastNameBegin;
Global StringVar LastName;
If IsNull({HOUSEHOLD.HSHLD_LAST_NAME}) then
   LastName := ""
Else
(
    LastNameBegin := InStrRev({HOUSEHOLD.HSHLD_LAST_NAME})," ");
    LastName := Mid({HOUSEHOLD.HSHLD_LAST_NAME},LastNameBegin+1);
);
LastName
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Sorry, CR7 doesn't have the InStrRev function.

Try this one

Local StringVar FullName := {FullNameField};
Local NumberVar LastNameStart := 1;
While InStr(FullName," ", LastNameStart) > 0 do
(
   LastNameStart := InStr(FullName," ", LastNameStart) + 1;
   FullName := Mid(FullName,LastNameStart);
);

FullName

mlmcc
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

Commented:
Thanks again for the help.  Unfortunately this has a small problem as well.  When I try the formula I get the following error:

"The remaining text does not appear to be part of the formula."

The cursor is placed several spaces following the =1; on the second line.  "While" is not recognized as a function.  I tried using WhileReadingRecords and WhilePrintingRecords as well but that didn't work either.  Do you have any other ideas?  

Thanks again for the help.

J
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
CR7 doesn't have any looping in formulas.

Is the 5 part name the worst possible name?

Try this one
Local StringVar strLastName;
Local NumberVar LastNamestart;

strLastName := {HOUSEHOLD.HSHLD_LAST_NAME};

LastNamestart := InStr(strLastName," ") + 1;
strLastName := Mid(strLastName,LastNamestart);

LastNamestart := InStr(strLastName," ") + 1;
strLastName := Mid(strLastName,LastNamestart);

LastNamestart := InStr(strLastName," ") + 1;
strLastName := Mid(strLastName,LastNamestart);

LastNamestart := InStr(strLastName," ") + 1;
strLastName := Mid(strLastName,LastNamestart);

LastNamestart := InStr(strLastName," ") + 1;
strLastName := Mid(strLastName,LastNamestart);

mlmcc

Author

Commented:
THANKS!!!

It worked perfectly.  I really appreciate all of the time you spent on helping me with this issue.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I finally had to get my CR7 machine out and look at the options.

If you need to handle names with potentially more parts just keep adding the pair of lines.
The one I wrote will handle up to 6 part names.

mlmcc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial