Parsing Names in a table?

[foxhound]

Does anyone know how to parse a name into 6 parts: salutation, first, middle, last, pedigree and degree using code with no forms? I would like to take the results and put them into a temporary table then union it with the original table into a new table where the new columns “TITLE,” “FIRST,” “MIDDLE,” “LAST,” PEDGREE,” and “DEGREE” are added.

1. The names in my table are currently set up as follows:
Duck III, Donald Q.
Mouse, Minnie M
Rabbit Jr, Roger T.

2. What if the names are set up as follows?
Mr. Rolland B. Apple III, PHD

3. What if set up this way?
Denison Jr, SPHR, Mr. Marshall A

4. Is there a way to test for all of the above conditions and separate the same way not matter how the name is listed in the table?

 

[Parra]

If this is a one time thing, then it can be done in excel and then imported back to Access. I'll let you know how.

If the table will be updated and you need to seperate the data on a regular basis, I know a query can be created, but I have to look into it a bit.

Let me know, I'm going out to lunch but will check the board for your response in about an hour.

 

[foxhound]

This information will be updated regularly with new names being added to the table. I looked through MSDN and found an article on how to parse names into multiple fields if the name is listed as shown in question #2 below. However, it requires using a form where only one name can be entered at a time.

 

[Parra]

I have a couple of things to do at work.

But let me get you started, in the criteria of a query. You would use the Left, Mid and Right functions to seperate the field. You will also use the Len function to know where to seperate the field. So it would be something like: Left(len([field])).

 

[foxhound]

Thanks for the start

Hi. Are there any additional details that someone might be able to give? :D