Separating Last Name, First Name + Initial in Access Table

I have a field called "Full Name" in a table which contains data in the following format:


The [INITIAL] is always one character or is a Title suffix as SR or JR

I would like to create a query that splits the "Full Name" filed into 2 fields:

- First Name (eliminating the one character initial or SR/JR but retaining any other second name)
- Last Name

and then I would like to create a field joining

First Name - Space - Last Name
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What have you tried so far? This is a fairly simple exercise in using the various Text functions like Left, Right, Mid etc.

For example, to move the FirstName to a new column named "sFName":

UPDATE YourTable SET sFName = Left([First Name], InStr(1, "," [First Name])

The syntax may be off a bit, but you should get the picture.


How do you determine what "any other second name" might be? For example, if my name is Bobby Jo, how would code know that it's different from Bobby Jr? You could look for specific instances of characters (like JR, SR, etc) but it's far from perfect.
Or Max Von Sydow ?
Patrick MatthewsCommented:

As the other Experts have mentioned, name parsing is actually a very tricky little exercise.

You may want to have a look at my article on the subject:

It will still have trouble with things like name parts having >1 word, but so will all other approaches.  It does do a fair job of picking up common titles and suffixes.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fitalianoAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.