How to parse name field into first,middle,last and suffix fields 1

[bigtbone]

I have an imported table with several thousand records into an ms access database that has a table with only fullname in it. I need to build a new table using a make table query that separates fullname into first name, middle initial, last name, and suffix. Here is what I have and need:
Full Name First Mid Last Suffix
Jon Doe Jon Doe
Jon B Doe Jon B Doe
Jon Bob Doe Jon B Doe
Jon Doe Jr Jon Doe Jr
Jon B Doe Jr Jon B Doe Jr
Jon Bob Doe Jr Jon B Doe Jr

I seem to get first name by having the following query
FirstName: Mid$([FullName],1,InStr([FullName]," ")) but I cant seem to get the rest of my name fields working correctly. Can someone help me? Thanks in advance!

 

[Remou]

There is a FAQ

http://www.tek-tips.com/faqs.cfm?fid=6468

 

[lameid]

Typically, I solve this sort of problem by making the table with everything but the name data and then begin updating.

I would probably start with suffix.

Where [FullName] Like "* Jr"

I would use a where statement like the above to identify the individual suffixes and update the suffix field in the related table appropriately. Naturally you'll have other possibilities like Jr., SR, SR. and possibly others (MD or other associated degree indicators). Once you have the Suffixes updated, I would use that information to trim full name down to just the name. From there, I would update the names based on whether there where 2 to 3 elements left.

Something like the below should give you the ones that have three elements.

Where Instr(Right([FullName], Len ([FullName]) - Instr([FullName], " "))," ") > 1

Hopefully that is enough to get you going.

Two more thoughts. Trim comes in handy in these situations as sometimes it is just easier to trim than to add or subtract to kill spaces.

Secondly, you show only wanting the middle initial and not the middle name. Some people use there first and middle names together like it is a first name. This may be important if your purposes are to be customer friendly. Unfortunately in these cases someone has to talk to the person.

 

[lameid]

Or use the prebuilt functions in the FAQ... I hate cross posting <sigh>.

 

[Remou]

 

[bigtbone]

How am I supposed to use the code in the FAQ in access?

 

[lameid]

I have not looked at it closely but you would copy the text from the code sections and paste them into a module in access. Just make sure you do it one after the other.

Each one of those code windows has a function (like instr) that takes value(s) and returns a value. You should be able to use those to get your fields.

Note that the code wraps inappropriately on the screen in the FAQ but when you paste it, it will be fine.