reformatting a name

[buckpeace]

i have a field in a table that contains the name "smith, john m". (NOTE:
this name is all in one field...the 1st and last names are separated by a
comma, but the middle initial is not separated with a comma )

I need to put the name in 3 different fileds:
first name john
last name smith
middle initial m

Can anyone help me split up this name using an ACCESS Query????

I really appreciate it

 

[Jason Lepack]

The field with your full name is nf

nf = "smith, john m"

first name = Left([nf],InStr(1,[nf],",")-1)
middle initial = Right([nf],1)
last name = Mid([nf],InStr(1,[nf]," ")+1,InStrRev([nf]," ")-InStr(1,
[nf]," ")-1)

SQL:
SELECT
Left([nf],InStr(1,[nf],",")-1) AS first_name,
Right([nf],1) AS middle_initial,
Mid([nf],InStr(1,[nf]," ")+1,InStrRev([nf]," ")-InStr(1,[nf]," ")-1)
AS last_name
FROM Table1;

Cheers,
Jason Lepack

 

[John Vinson]

i have a field in a table that contains the name "smith, john m". (NOTE:
this name is all in one field...the 1st and last names are separated by a
comma, but the middle initial is not separated with a comma )

I need to put the name in 3 different fileds:
first name john
last name smith
middle initial m

Can anyone help me split up this name using an ACCESS Query????

I really appreciate it

Well... beware. I have a friend whose first name is Rhoda Mae. It's
not Rhoda, and her middle name is not Mae - her first name is Rhoda
Mae, just ask her!

That warning given: if you add new text fields FirstName, LastName and
MiddleInitial (or MiddleName) to your table, you can run an Update
query:

UPDATE yourtable
SET LastName = Left([fullname], InStr([fullname], ",") - 1,
FirstName = Mid([fullname], InStr([fullname], ", ") + 2)
WHERE [fullname] LIKE "*, *";

followed by another query to split the first ane middlenames:

UPDATE yourtable
SET FirstName = Left(FirstName], InStr([FirstName], " ") - 1),
MiddleName = Mid([FirstName], InStr([FirstName], " ") + 1)
WHERE FirstName LIKE "* *";

This second query will ignore those records with no middle initial.

John W. Vinson[MVP]