Divide One Field into 3 parts

  • Thread starter Thread starter KrispyData
  • Start date Start date
K

KrispyData

I have a Name field that I would like to divide into 3 seperate parts?

example:
John W Smith
Lisa Marie Jones

I would like the the result to be 3 different fileds: First, Middle, Last
Name

Is this even possible?
 
I have a Name field that I would like to divide into 3 seperate parts?

example:
John W Smith
Lisa Marie Jones

I would like the the result to be 3 different fileds: First, Middle, Last
Name

Is this even possible?
Click to expand...

Possible but tedious. What will you do with John Ronald Reuel Tolkien? Or
Madonna? Or Rhoda Mae Johnson (whose first name is Rhoda Mae, just ask her)?
With someone who has no middle name?

I'd suggest adding FirstName, MiddleName and LastName fields to the table, and
then run a series of update queries. First update FirstName to

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

using a criterion of

LIKE "* *"

on name to be sure there IS a blank in it. In the same query update LastName
to

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

Then in a second query, use a criterion of

LIKE "* *"

on LastName and update MiddleName to

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

and LastName to

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

Then do a search of all three fields using a criterion of

LIKE "* *" OR IS NULL

to find any remaining odd blanks. You should find NULL values only for
MiddleName (for people with no middle name) but there might
 
Possible, yes.

Easy, maybe...

Are you very VERY certain you don't have any names like:

Cher
Jean Claude van Damm
Sir Edmond Hillary

As you can imagine, trying to run one of those names through your '3 parts'
routine would lead to incorrect results.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Steve said:
It's not foolproof because of the structure of certain names but in your
example you can use the Instr function to look for spaces to separate the
name into parts. Look at the Instr function in the Help file.

Steve
(e-mail address removed)
Click to expand...

Anyone else noticed that our old "friend" Steve is advertising in every
post via the From: field in the message header?

Tsk, Tsk.

These newsgroups are provided by Microsoft to allow enthusiasts to
assist beginners (and each other), not as a shop-front.

Phil, London
 
I'd suggest adding FirstName, MiddleName and LastName fields to
the table
Click to expand...

I long ago abandoned middle name/middle initial fields. That's not
data that you're ever going to search on by itself, so you save
yourself all sorts of concatenation issues, though with Trevor
Best's Mid() Null propagation trick, it's not that hard:

Mid(("12"+LastName) & (", "+FirstName) & (" "+MiddleName), 3)

(there is a problem with that, because for it to work reliably, you
need the same number of characters null-propagated with each field.
But you'll never have a middlename without a firstname, so this
works).

(I also use it for addresses:

Mid(("12"+Address1) & (CrLf()+Address2) & (CrLf()+Address3), 3)

[CrLf() is a function that returns vbCrLf]

Given that your newline is two characters, it works out pretty
well.)
 
Hi John:

I'm a little confused. Are you suggesting I create an update query and use
the expression you indicated?
Also, I tried going on dfenton's website and it was a violinist site??
 
Hi John:

I'm a little confused. Are you suggesting I create an update query and use
the expression you indicated?
Also, I tried going on dfenton's website and it was a violinist site??
Click to expand...

If you're trying to update LastName, FirstName etc. fields in your table, then
yes, you will need update queries. Reread my message: I suggested two update
queries run in succession.

No idea about the website.
 
David has multiple interests and abilities. The first link has a link at
the bottom along the lines of "How I earn a living", which goes to the
consulting web site, which has various examples and tips, as I recall. The
second link goes directly to the consulting web site.
 
Back
Top