I have a table with a "Name" field that contains the First, Last and Middle
Initials and Names in it. I would like to know how I can split these into 3
seperate fields. Some of the names have titles with them as well. What are
the xpressions that i need to build in order to seperate into 3 fileds
accurately? Please Help! Very Confused!
That's very difficult to automate. I have a friend named Lorna Sue Jones (not
her real last name); but her first name IS Lorna Sue, it's not Lorna and
she'll correct you if you call her that. Ludwig von Beethoven's middle name
was NOT von; his last name was von Beethoven. Your best bet is USB - Using
Someone's Brain.
You can get a *start* on it and handle some of the most common cases using a
series of queries. Let's say you have a field named [Name] (a bad idea by the
way, it's a reserved word), and new empty fields [FirstName], [MiddleName],
[LastName], [Title] and [Suffix]. You could run an update query to find all
[Name] values containing exactly one blank and fill the [FirstName] and
[LastName] fields with a query:
UPDATE table
SET [FirstName] = Left([Name], InStr([Name], " ") -1),
[LastName] = Mid([Name], InStr([Name], " ") + 1)
WHERE [Name] LIKE "* *"
AND [Name] NOT LIKE "* * *";
Open the VBA window by typing Ctrl-G and use Help to get documentation on
Left, Mid and InStr. The criterion finds all names with one blank but excludes
names with two or more blanks.
You can also search for names
LIKE "Mr.*" OR LIKE "Ms.*" OR LIKE "Mrs.*" OR LIKE "Dr.*"
to extract the title (using the same sort of logic). Three part names take a
more complex set of Mid() function calls but can be managed; however - given
the examples above - I'd really suggest handling them manually if at all
possible.
