Re: Firstname Lastname - Lastname, Firstname

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 03/18/04 

Date: Thu, 18 Mar 2004 10:58:56 -0700

On Thu, 18 Mar 2004 08:39:07 -0800, "Gary"
<anonymous@discussions.microsoft.com> wrote:

>Hello!
>I inherited a database in Access 2000 with the employee
>names listed as Firstname Lastname. Is there an easy way
>to convert that data to Lastname, Firstname.
>
>Ie: Change Gary Weiss to Weiss, Gary.
>
>Thanks in advance!
>Gary

This is why most developers will have separate fields for first and
last names: it's much easier to concatenate two fields than to
separate a single field, and it makes it much easier to (say)
alphabetize a list by last name. Some of the problems you'll
encounter:

Joe Bob Smith ' what's his first name? Everyone calls him Joe Bob
Mark van Kleef ' but he's Mark, last name van Kleef

That said - you can get the simple two-name cases turned around using
Access' string functions:

TurnedName: Trim(Mid([EmpName], InStr([EmpName], " "))) & ", " &
Left([EmpName], InStr([EmpName], " "))

You can (and probably should!) add new FirstName and LastName fields
to your table; run an Update query updating FirstName to

Left([EmpName], InStr([EmpName], " ") - 1)

and LastName to

Trim(Mid([EmpName], InStr([EmpName], " ")))

and then fix up the Joe Bob and other special cases.

                  John W. Vinson[MVP]
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public