Sorting last names 1

[seanprichard]

I have a report that is basically a long list on names, sorted alpha by last name. The problem is, if a last name has a space or a ' in it like MAC DONALED or L'Donnavon, those names don't sort correctly. For example, L'Donnavon would be with 'LA' instead of with 'LD'.

Anyone know of a way to sort these types of names along with the others?

Thanks!

Sean

 

[synapsevampire]

Create a formula for sortation such as:

whileprintingrecords;
stringvar LName:={table.lastname});
Stringvar NewLName:="";
numbervar Counter;
For Counter := 1 to len(LName) do(
if asc(mid(Lname,Counter,1)) in [65 to 90]
or
asc(mid(Lname,Counter,1)) in [97 to 122]
then
NewLName:=NewLName+mid(Lname,Counter,1)
);
NewLName

Use this for sorting/grouping, use the last name field for display.

-k

 

[kcgamz]

Someone may have a better idea, but this will work if you have a limited number of "non-alpha" characters that you might find in a name (like "'", "-", and " ").

Create a formula like this

If InStr ({USERS.LAST_NAME}," ") <> 0
Then
Join(Split ({USERS.LAST_NAME}," "),"")
Else
If InStr ({USERS.LAST_NAME},"'") <> 0
Then
Join(Split ({USERS.LAST_NAME},"'"),"")
Else
If InStr ({USERS.LAST_NAME},"-") <> 0
Then
Join(Split ({USERS.LAST_NAME},"-"),"")
Else
{USERS.LAST_NAME}

You can piece together as many "else if" clauses as you need to cover all the "non-alpha" characters you might encounter. Then you can sort or group on the formula, but display the actual name field in your report.

Jerry

 

[synapsevampire]

I took the opposite approach, Jerry, if it isn't a valid character (A-Z or a-z) then it's tossed.

-k

 

[kcgamz]

Yeah, I just tossed out the first thing that came to mind. I like your approach a lot better. Thanks.

Jerry

 

[kenhamady]

There is a free UFL listed on my web site's LINKS page that has several functions in it. One strips out spaces and the other strips out all punctuation. You could nest one within the other to do this.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com