Single Field into Multiple Fields

[Mike Hughes]

I'll also try this another way (see my other post today)

I found this information on how to split a single name field into multiple fields, but the article only talks about first and last names. I have a table where some of the names also have middle initials.

This is what the article said:

The following example demonstrates this using Full Name, where you wish to store this as First Name, Last Name:
We begin with by creating a new query, and adding the Contacts table (that is currently storing Full Name as a single field) to the query design.
We have added the FullName field to the query design, then we need to add two further fields, which we will create for FirstName and LastName and will use the following string expressions:
FirstName: Left([FullName],Instr([FullName]," ")-1)
LastName: Mid([FullName],Instr([FullName]," ")+1)

Could someone give me some suggestions on how to include middle initials (where a name has them) into this expression? So to go over what I have, I have a single field which contains the workers full name which may include a middle initial and I want to convert it to multiple fields.....sorry for all the questions.

Mike

 

[ecniv]

Hmmm, in Sql statements, not likely.
To get first n last possible, as these are always there, but the middle names could or not be there.

Its possible in code, and probably wouldn't take too long to write and run the loop to fill a table if required.


Vince