Sorting a field that contains both first and last names in Access

[scott_tucker26]

Hi,

I have a name field that contains both first and last names. How can I sort
the last name alphabetically when they vary in size. For example one field
has "Charles Yee" and another has "Tom Fredericks".

Any help will be much appreciated.

 

[Damian S]

Hi Scott,

You could attempt to use the Left and Mid functions to split the name at the
first space it finds, then sort by those results. It will be variable as
some people might have a space in their name eg: Mary Sue, but it will be
close. I guess this is why a good design is to have names split into first
and last name.

Here's a sample:

left("bob smith", instr(1, "bob smith", " ") - 1) will return "bob"
mid("bob smith", instr(1, "bob smith", " ") + 1, len("bob smith")) will
return "smith"

Try it!!

Damian.

 

[scott_tucker26]

Hi Damian,
Thanks for that. What would you suggest if I need to apply that to all the
records?

Scott

 

[Damian S]

Hi again Scott,

You would just need to replace the "bob smith" with [FIELDNAME] where
FIELDNAME is the name of the field you are trying to sort on. Use this in
your query.

Damian.

 

[fredg]

Hi,

I have a name field that contains both first and last names. How can I sort
the last name alphabetically when they vary in size. For example one field
has "Charles Yee" and another has "Tom Fredericks".

Any help will be much appreciated.

Of course the correct solution (and the simplest) is ** do not have a
field that contains both names **. You should have a FirstName field
and a LastName field. The sooner you correct your faulty database
design, the easier your database programming will be.

You could sort on
LastName:Mid([FullName],InStr([FullName]," ")+2)
FirstName:Left([FullName],inStr([FullName]," ")-1)

But this will not correctly sort on names like Robert Louis Stevenson,
nor Edna B. Ferber.

 

[BigJohn]

I think we have all been in this place and it is not fun. Here is what I had
to do:

Create a module function(strInput as variant) as string
Parse the string backwards until the first space is reached
Return data to the right of the last space "should" produce what we all
assume is the common concept of a last name.
Be sure to check if a space exists in the name after first checking if the
value is Null. A null value should return "" and a value without a space
should return the input value (assume it is the last name). Of course if the
input string length is zero, just return "".

You will take a hit on response time, but it produces more accurate results.

Hope this is helpful.