Adding a comma after Last Name

[Guest]

Hi there,

Hope someone can help me with the followings data:

Name
Smith Ann K.
Snyder John

I would like to add a comma after the last name, so that it will show:
Smith, Ann K
Snyder, John.

I can separate the name into last, first, and middle name, then concatenate
the last name, a comma, first name, and middle name. But I don't like to do
that.

Is there another way just to add a comma after the last name without go into
the trouble separate the name and then concatenate them again. Thanks in
advance for your help.

 

[Guest]

You might not want to do it that way; however, splitting the name into
FirstName, LastName, and MI fields is best. It gives you many, many more
options when working with the names later on. It also simplifies the problems
if you ever need to export the names into a comma seperated value file.

 

[Rick B]

You say you don't like to separate the name, but it should be separated.
Each field should contain one piece of data. Your structure is storing
three pieces of data in one field. What will you do with a name like John
van der Beek? You should Normalize your data.

 

[Guest]

Well, if there is no other way to add a comma except concatenate then I have
to do the long way. Thanks for your advices.

 

[John Spencer]

You could always replace the first space with a comma and a space if you are
using later versions of Access (2000 SP 3 or later) if I recall correctly.
Replace didn't work correctly in queries in the early versions of 2000.

Replace("Spencer John A"," ",", ",1,1,1)
Spencer, John A

But

Van Allen William would be Van, Allen William

As others have said, if it is possible it would be better in the long run to
parse the name into its pieces so you can combine them as needed.