Can I extract part of a variable length text field based on spaces

B

Brunmac

My file has a text field that contains First Name space Middle Initial space
Last Name. I need to create new fields...one containing the First Name, one
containing the Middle Initial (if there is one), one containing the Last
Name. For instance:

Existing Field Data New First Name Data New Mid Init Data New Last Name
Data
Humphrey A Bogus Humphrey A Bogus
Hubert Smith Hubert
Smith

Is there a way to do this in Microsoft Access 2003?
 
O

Ofer

From your example, I can see that you don't always have a Middle Initial, in
that case the application will put last name as Initial
You can specify that you want to break the string into three parts, and to
assign each part to a different field, but if you have in a string only two
part, you can't expect Access to think for you and put each part of the name
in the right place.

To break the string into parts, check help on the function "Split"
 
J

John Vinson

My file has a text field that contains First Name space Middle Initial space
Last Name. I need to create new fields...one containing the First Name, one
containing the Middle Initial (if there is one), one containing the Last
Name. For instance:

Existing Field Data New First Name Data New Mid Init Data New Last Name
Data
Humphrey A Bogus Humphrey A Bogus
Hubert Smith Hubert
Smith

Is there a way to do this in Microsoft Access 2003?
Click to expand...

Yes... but.

My friend Rhoda Mae's first name is Rhoda Mae, not Rhoda. Just ask
her.

My former coworker Dr. Felix de la Iglesia's last name is de la
Iglesia. His middle name is not "de".

There is NO easy and general way to do this, *even for a human being*
- if I saw the name "Wong Lee Fun" I'd simply have to ask the person
if their family name is Wong and their personal name Lee-Fun, Chinese
style, or if the family name is Fun.

That said... you can get rid of the COMMON cases using a series of
Update queries. Add your new FirstName, MidInit, and LastName fields
to your table. First run an Update query updating FirstName to

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

and LastName to

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

To protect yourself from errors from names like "Cher" or "Madonna",
use a criterion of

LIKE "* *"

on the ExistingField.

Then, run a second update query; use a criterion of

LIKE "* *"

on LastName to include only two-word last names. Use the same kind of
Left and Mid logic as above, only on the LastName field, updating
MidInit and LastName.

THEN - run a Query searching for MidInit IS NOT NULL to find all the
"Mae" and "de" and "von" and so on, and fix them manually. It may be
necessary to ask the person (politely) what name they prefer.

John W. Vinson[MVP]
 
Top