split name field into parts (1 Viewer)

I'm trying to build a query function to split a [FullName] Field in a table into it's parts--[Prefix], [FirstName],[Middle],[LastName],[Suffix] in the same table. Unfortunately, the [FullName] comes to me in many different configurations:

"John Smith"
"John M. Smith"
"Mr. John Smith"
"John Smith, MD."
"Mr. & Mrs. John Smith"
"John and Mary Smith"
"John Smith and Mary Ann Wilson-Smith"
"John M. Smith and Mary Ann Wilson"
"Dr. Mary and Mr. John Smith"

,etc.

So far, I'm looking for " and " or " & " to determine if there's one name or two, and looking for "-" or "," to determine if there's a hyphenated name or a suffix. Am I on the right track, or is there a better way to start?

Can someone point me in the right direction?

Thanks,
Sup

You're on the right track meaning you'll need a single approach per instance.

RV

i was looking into that recently. As with most things, it's been done by someone....so for a small fee, buy a MS Access add-in http://www.infoplan.com.au/splitter/ which is pretty good, or use this code http://eis.bris.ac.uk/~ccmjs/accmisc.htm#splitname

Good luck.
Doc

Having just tried the code, I thought I should let you know what I found.

The name formats I was splitting was SURNAME then INITIALS with only spaces...no commas.

Because the format is reversed, I had to use the get_title to extract the surname and the get_surname for the initials. Everything works well except for surnames with multiple parts such as Van Der Bilt.

To fix that, run a query using Len() to determine the length of the original full name field, with criteria

<>Len([Surname])+Len([Initials])+1

which creates a numeric value based on length of initials plus surname plus the space between.