splitting a full name into first name; last name in access 2003

[Shusha52]

I am sorry at sounding repetitive.I cannot see the question i posted so i am posting.
Could you please help me on how separate a fullname into 2 fields. Let say the full name was TOM Jones. And it is in a name called FULLNAME
I want to separate that name into tow fields namely lastname and firstname where i wld like to see TOm under first name and Jones under the seconds name.
How can i do that. I have over 2000 records and it is easy to run a query and update two new fields namely first name and last name with the full name and then manually take out the first name and then last names in the corresponding fields thereby having three fields.
Is there an easier way to run a query that will split them.
I am not sure.
Any help is appreciated
Usha

 

[ThereUare]

You can put this in your Query. Just make up a new field like this:

FirstName: Left([FullName],Instr([FullName]," ")-1)
or
LastName: Mid([FullName],Instr([FullName]," ")+1)

Hope that helps.

 

[Shusha52]

Thanks for your prompt response. I tried it and it works for some and for some it did not.. atleast i dont have to go through couple of thousands to see if it worked.. Your help is muc appreciated and thanks for the same
Usha

 

[missinglinq]

Parsing names is never going to be 100% accurate! There're simply too many variations when entering names where they have more than two components, i.e. Vincent van Gogh, Thurston Howell III and so forth. The best you can do is use a hack like ThereUare gave you and then manually inspect/correct the records that it didn't work on. Parsing addresses that have been crammed into a single field provides the same challenge.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Remou]

Have you seen:

faq705-6468

 

[missinglinq]

I hadn't seen that, but I've actually done everything given there for someone. It was a royal pain developing and a manual inspection /correction still had to be done. There's simply too many variations in the way names are entered, and the more records you're talking about the more, as a rule, data entry people have been used, which means that many more styles of entering the names.

Which is why, of course, we advise people developing a db to use separate fields for the name component.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!