Split FirstName-MiddleName-LastName up 2

[james33]

Dear All,
I have some data in a One Field Table (someone else's honest)! and they have managed to put all the customers names in that field.
There may even be some with a Mr. or Ms, there too!
The one commonality with them all, is that there is a space between each word, amn't I too lucky ;-)
Well if I say that the maximum number of words in the names is 5, would anyone have some pointers for a Query/Method to split them up into 4 fields, 5 if you include Mr Ms.
I guess a few nested ifs (and buts), my apologies but I have been away from Access for a while and I am just getting back to the joy of it all.
I enjoy a challenge but I do need a shove in the right direction, I have checked the Forum and FAQ pages here but they are quite extensive so I haven't managed to track down any relevant ones.
Thanks in advance for any and all ideas.
Jim

 

[Remou]

Hi
Here is a nice, long debate on just that topic: Separating Name Query thread701-794191

 

[SkipVought]

Hi,

Are there first names like
[tt]
Mary Jane
Joe Bob
[/tt]
Are there last names like
[tt]
van Gundy
[/tt]
Are there
[tt]
Jr
Sr
III
[/tt]
on some?

In other words, can you predict, with certainty, that, if you delimit by spaces, the positions represent:

1) title
2) first name
3) middle name/initial
4) last name
5) name suffix



Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[james33]

Dear Remou & SkipVought,
Thank you both for your ideas, Remou, I will check out the thread you mentioned
and
SkipVought I guess an idea for the 4 parts would be sufficient as once I have the situation for 4 I should be able to extrapolate (hopefully).
I am completely certain that while there might be leading and trailing spaces and commas or periods in the prefix they will all either be Mr Ms Mrs Rev or nothing there
Yes skip I realise that & quoting you:
"In other words, can you predict, with certainty"
That jnr would be missed but I can deal with that once I have something to go on.
The upshot of it is, I would as I stated initially really appreciate any insight, or a poke in the right direction.

Thanks again
Jim

Regards Jim
Check the FAQ area of this Forum for answers (If there are any FAQs!) , it could save you a lot of time and effort!

 

[SilentAiche]

James33,

For what it's worth, I was provided some valuable assistance in splitting name fields a few months ago in [blue]thread181-1001617[/blue]. My issue was reversing name order, but the solution should be easy to adapt to your needs.

Good luck!
Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[Oz1127]

Maybe this can help

REVERSE NAME: Trim(Mid([NAME],InStr(1,[NAME],',')+1,50))+' '+Trim(Left([NAME,InStr(1,[NAME,',')-1))

FIRST NAME: Trim(Mid([NAME],InStr(1,[NAME],',')+1,50))

LAST NAME: Trim(Left([NAME,InStr(1,[NAME,',')-1))

The first one will put the first name first, or vice versa.