Convert Full Name field to Separate First, Middle, & Last Names

[Leslie]

I am trying to atomize a database so that I can build a
more efficient search function. I know how to separate the
First name from the Last name using two functions:

FirstName: Mid([tblOrders]![FullName],1, _
InStr([tblOrders]![FullName]," ")-1)

LastName: Mid([tblOrders]![FullName], _
InStr([tblOrders]![FullName]," ")+1)

.. . . but I don't know how to extract the Middle name and
it ends up with the Last name in the LastName field. (I
would actually most prefer to have only two fields - one
that contains the First and Middle names, and one that
contains the Last Name and any suffixes, but will break it
into three or more fields if necessary). I also get an
"#Error" message in the new FirstName field of there is
only one name in the FullName field to begin with (which I
am assuming would happen many times over if I tried to use
a function similar to the FirstName function to extract the
Middle name from the new LastName field).

I should also note that the table I am extracting this from
already has many thousands of records and that I will be
needing to carry out this data modification many times over
as data is being imported into the table.

Any suggestions?

Many thanks for your valuable MVP assistance!

 

[John Nurick]

Hi Leslie,

A crude but fairly effective approach that works for most traditional
American names is:

1 Assume that everything up to the first space is the first name.

2 If there are no other spaces, the remainder is the last name.

3 If there are spaces, check for particles such as "de", "de la", "van",
"van der" and so on. If the remainder fits one of these patterns, assume
it's the last name. If it doesn't, move on to the next space and loop
back to 2.

4 Assume that everything between the first space and the beginning of
the last name is the middle name.

To do much better than this means a *lot* of programming. Consider using
a commercial tool such as Splitter For Microsoft Access
http://www.infoplan.com.au/splitter/. This isn't perfect (as I said,
nothing can be) but at least someone else has done the hard work<g>.

The problem is that you can't trust people to have names that fit the
pattern:
Pierre Joseph Marie Teilhard de Chardin
Boutros Boutros Ghali
Emmanuel Le Roy Ladurie
J. Edgar Hoover
Laurens Van Der Post
Sitting Bull
to name but a few. And there are billions of people in the world (the
majority, as far as I know, whose names don't fit the traditional
American "first middle last" pattern at all: for instance all the places
where the family name is put first and the personal name(s) last.

In addition, there are names that are written identically but parse
differently:

Mary Patrick Henry (daughter of devoutly Catholic
Mr & Mrs Henry, Patrick is middle name)
Mary Patrick Henry (family name is "Patrick Henry", let's
not ask why)

So there's no possibility of writing code that can do this job
perfectly.

I am trying to atomize a database so that I can build a
more efficient search function. I know how to separate the
First name from the Last name using two functions:

FirstName: Mid([tblOrders]![FullName],1, _
InStr([tblOrders]![FullName]," ")-1)

LastName: Mid([tblOrders]![FullName], _
InStr([tblOrders]![FullName]," ")+1)

. . . but I don't know how to extract the Middle name and
it ends up with the Last name in the LastName field. (I
would actually most prefer to have only two fields - one
that contains the First and Middle names, and one that
contains the Last Name and any suffixes, but will break it
into three or more fields if necessary). I also get an
"#Error" message in the new FirstName field of there is
only one name in the FullName field to begin with (which I
am assuming would happen many times over if I tried to use
a function similar to the FirstName function to extract the
Middle name from the new LastName field).

I should also note that the table I am extracting this from
already has many thousands of records and that I will be
needing to carry out this data modification many times over
as data is being imported into the table.

Any suggestions?

Many thanks for your valuable MVP assistance!

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.