Splitting names

T

Todd

Hi All,

I trying to write a query that splits a person's name into first, middle and
last name, all this data is currently stored in one field - i want i over 3
can anybody suggest how to do this.

Thanks
 
R

RobFMS

Todd

This is probably just 1 example on how to solve the problem.

Assumptions:
1. There is a space that separates each name. If not, change the delimiter
in the Split() function.
2. There is, in fact, first-middle-last in the data.

Here is the sample table (named Table2)

Field: PersonName
-----------------
John A. Anderson
John B. Bradley
John C. Clark
John D. Dennis

Here is the code to put in a public module
-----------------
Public Function GetFirstName(ByVal pstrName As String) As String

Dim arrName() As String

arrName = Split(pstrName, " ", , vbTextCompare)

GetFirstName = arrName(0)
End Function

Public Function GetMiddleName(ByVal pstrName As String) As String

Dim arrName() As String

arrName = Split(pstrName, " ", , vbTextCompare)

GetMiddleName = arrName(1)
End Function

Public Function GetLastName(ByVal pstrName As String) As String

Dim arrName() As String

arrName = Split(pstrName, " ", , vbTextCompare)

GetLastName = arrName(2)
End Function



Here is the query to execute
-----------------
SELECT
GetFirstName(Table2.PersonName) AS FirstName,
GetMiddleName(Table2.PersonName) AS MiddleName,
GetLastName(Table2.PersonName) AS LastName
FROM Table2;


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Top