split one field into 3 fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

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...
 
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,
 
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
 
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...
Click to expand...


.
Click to expand...
 
Back
Top