Splitting Names Problem

[Ed Peluffo]

Hello all and thanks to those that helped my with my question last time.

Have the following problem with a names store in only one field (NAME)

The names are store in more than one format as follows:

F LAST (two spaces between first and last)
F M LAST
F M DEL LAST (Last name is two parts divided by spaces)
F.M. LAST (Periods and no spaces)
FIRST M LAST
FIRST LAST JR (JR suffix at the end)
FIRST M. LAST
FRIST & FIRST LAST (Husband and Wife)

As you can see, the only thing that is consistant is the use of the first
inital or first name first. Otherwise the name can appear in many different
formats.

Has anybody written a Sub or Query to deal with this kind of mess. My goal
is of course to split the names in to FIRST, MIDDLE, LAST, SUFFIX fields.

I have tried using the Instr, Left, Mid, Right functions but can't get them
to work correctly, I can't access my Access 2000 Visual Basic Reference Help
files for some reason, anybody know what file and location it is that I need
to look for or how to get the Visual Basic Function Reference to come up

Any input or help as how to deal with this problem is much appreciated.

 

[GreySky]

Sooooo. How much is it worth to ya? tee hee hee.

I've written a name parser that does, quite literally,
exactly what you've asked for -- right down to the husband
and wife part.

Email me at (e-mail address removed), and I'll send it to
you. Home is (e-mail address removed) (please send to my
work address though).

And yes, it's freeware. And yes, it rocks!

David Atkins, MCP

p.s. This is the most requested stand-alone function I've
ever written. Ironically, it's one of the first functions
I wrote as a professional developer (I used to do "data
conversions").

 

[GreySky]

Mr. and Mrs. David C. and Nancy Atkins, Jr., EVP
Smith, A. M.; Jones, T.; Tompson, J. T.
M/M David C. & Nancy L. Atkins
Atkins, David and Nancy
Van Gough, Martin and Sheila
Martin & Shiela Mc Donald
Tom and Mary Smith
James T. Bringham, Ph.D.
David & Nancy Atkins
Mr. John P. Williford & Ms. Mary K. Smith, Ph.D.


--- These are some of the name variants my parser can
handle.

I also built an address parser that's equally as powerful.

If you're a doubter, get a copy first, then start
complaining about how it can't be done.

David Atkins, MCP

 

[Mark G. King]

Try Splitter for Microsoft Access. It's an Access Add-In that does what you
want.

www.infoplan.com.au/splitter