Extracting and Separating Names in Access Query

[Tanner]

I would like for a query to extract last name, suffix, first name and middle
initial (in that order) The data field is setup as first name middle initial
last name and suffix. Some names do not have middle initial or suffix.

Thank you.

 

[Allen Browne]

You can parse the name using Instr() to locate the spaces, Len(), Left(),
Right(), and Mid().

It gets messy, so you may find this ParseWord() function useful:
http://allenbrowne.com/func-10.html

Use ParseWord(1) to get the first name, and ParseWord(-1) to get the last
word (presumably the surname)

 

[John Spencer]

A really tough problem to solve accurately. Here are two examples
Mary Anne Bradford: The person's first name is Mary Anne. No middle name.
Oscar de la Hoya: No middle name

You can come close by using some VBA to split the string into words based on
the spaces. and then examine the word count to help you decide what you have.

Two words: First word is first name, second word is last name

Three or more words you have to start applying more complex rules
-- is Last word equal to Jr, Sr, III, IV, etc then it is a suffix and if not
it is the last name (or at least part of the last name)
-- If last word is a suffix and there are three words, then First word is
first name and second word is last name
-- If a word is one letter in length and is not the first or last word it is
the middle initial.
-- The first word is all or part of the first name. Subsequent words may be
part of the first name except for the last word that is not a suffix.

etc.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County