Separating name field into FNAME, MI, LNAME

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to know how one would go about taking string data in a "NAME" field and actually generating three fields (FNAME, MI, and LNAME). Do I need to create the fields in my table, first? Is VBA required? I really need for someone to walk me through the entire process if possible. Thanks in advance.
 
I would like to know how one would go about taking string data in a "NAME" field and actually generating three fields (FNAME, MI, and LNAME). Do I need to create the fields in my table, first? Is VBA required? I really need for someone to walk me through the entire process if possible. Thanks in advance.
Click to expand...

You will want to create the new fields first, but you won't need any
VBA: an Update query will do the trick.

Assuming (and you should check this assumption!!!) that the NAME field
contains names like

John Wilmot Vinson
Jaycee Jay
J. C. Jay
Kevin Lopear

I'd do it in three passes:

1. Run an Update query updating FName to

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

and LName to

Mid([Name], InStr([Name], " ") + 1)

Do this by creating a Query based on your table; select the Name,
FName, and LName fields, changing it to an Update query with the query
menu item, and putting the following expressions on the Update To
line. Run the query by clicking the ! icon. The InStr function finds
the position of the first blank in [Name]; Left and Mid extract
substrings up to and starting with the specified position.

This will give FNames of "John", "Jaycee", "J.", and "Kevin", and
LNames of "Wilmot Vinson", "Jay", "C. Jay", and "Lopear" - i.e. first
names right, middle and last together in Last.

Then, create a second update query using FName, LName, MI; put a
criterion on the Criteria line of

LIKE "* *"

to limit the updates to only those names where there is a blank in the
LName field; names like "Jaycee Jay" and "Kevin Lopear" don't need any
further processing.

The Update To line should have for MI

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

and for LName

Mid([LName], InStr([LName], " ") + 1)

The IMPORTANT third step is... MANUALLY go over this list and correct
the mistakes. THERE WILL BE MISTAKES. My friend Myrna Lou doesn't have
a middle name; her first name is "Myrna Lou", just ask her. And there
are two- and three-word last names such as "van Kleef" and "de la
Iglesia". Some names are very hard to distinguish without talking to
the person - is Jim Van Jones really Jim in the Van Jones family, or
does he just call himself "Jim Van", or is his middle name Van? Hard
to be sure!
 
Suggest you do it the opposite way. Set up three fields in your Access
table: FName, MName, LName and then create a query expression to return
FullName by concatenating the three pieces. You'll need an IIF statement to
check for a null value in MName so that you don't append extra spaces in the
event that there is no middle name; i.e., it's NULL. You could also add a
prefix and suffix field for Mr. or Jr., III, etc. and create a more complex
expression to concatenate these fields into a full name as well.

It's good practice not to have to enter it both ways. Either start with
individual fields and concatenate them via a query expression (my choice) or
enter a full name then parse it.
 
Back
Top