split one field into 3 fields

[Guest]

I need to split a name field in the format [FistName MI
LastName] into 3 new fields [FirstName], [MI], and
[LastName].

Does anyone have the code to do this?

Thanks...

 

[Cheryl Fischer]

Because of the variability of peoples' names, there is no single piece of
code that will make this split with complete accuracy; however, you might
want to try the following in an Update Query and then manually correct those
where the name did not fit the pattern. This code will work when your
FullName field has names like:

Mary Anne Smith
George C. Brown

It will not work for names like:

Janet M. van de Velde

For FirstName, put the following in the Update To: row of an update query:

Left([FullName],InStr([FullName]," ")-1)

For MiddleName, put the following in the Update To: row of an update query:

Mid([FullName],InStr([FullName]," ")+1,InStrRev([FullName],"
")-InStr([FullName]," ")-1)

For LastName, put the following in the Update To: row of an update query:

Mid([FullName],InStrRev([FullName]," ")+1)


hth,

 

[Gerald Stanley]

You can use the Split function to achieve this. If the 3
fields are delimited by " ", you could use code like

strNameParts = Split(strWholeName , " ")
strFirstName = strNameParts (0)
strMI = strNameParts (1)
strLastName = strNameParts (2)

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

Works perfect!! Exactly what I needed.

Thanks so much!

-----Original Message-----
Because of the variability of peoples' names, there is no single piece of
code that will make this split with complete accuracy; however, you might
want to try the following in an Update Query and then manually correct those
where the name did not fit the pattern. This code will work when your
FullName field has names like:

Mary Anne Smith
George C. Brown

It will not work for names like:

Janet M. van de Velde

For FirstName, put the following in the Update To: row of an update query:

Left([FullName],InStr([FullName]," ")-1)

For MiddleName, put the following in the Update To: row of an update query:

Mid([FullName],InStr([FullName]," ")+1,InStrRev ([FullName],"
")-InStr([FullName]," ")-1)

For LastName, put the following in the Update To: row of an update query:

Mid([FullName],InStrRev([FullName]," ")+1)


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I need to split a name field in the format [FistName MI
LastName] into 3 new fields [FirstName], [MI], and
[LastName].

Does anyone have the code to do this?

Thanks...

.