Given and Last Names Separation into each Column

[kevz]

I'm using Access 2007 and I'm working on the task of separating people's complete full name that is currently in one column/field. I would like to separate into 4 categories (1st given name, 2nd given name, 1st last name, and 2nd last name).

Problems I foresee are:
1. How do I separate Mary Ann Jane Smith that is in one column and place Mary in col.1 Ann in col.2 Jane in col.3 and Smith in col.4?
2. If the record only has 1 to 3 names which name(s) is/are missing?



Does anyone have any advice on how these tasks maybe accomplished. I Thank-you in advance.

[maximus]

This is a function that will seperate the names:
Syntax: SeperateName(Field having Names,Indicator)

indicator will be integers from 1 to 4

1=FirstName
2=Second Name
3=ThirdName
4=FourthName

Function:

Option Compare Database
Dim intCounter As Integer
Dim intIndicator As Integer
Dim strMytext As String
Dim MyNumber As Integer
Dim MyAlpha As String
Dim intBreak As Integer
Dim str1stName As String
Dim str2ndName As String
Dim str3rdName As String
Dim str4thName As String
Dim intStarter As Integer
'************************************************* ******
'*Function written by Siddthartha Majumdar on 04/29/2010
'*Scope can Seperate strings with spaces
'*e.g. Ann Joyce Smith can be seperate Ann,Joyce,smith
'*e.g. Ann Joyce Smith Jones can be seperated as Ann,Joyce,smith, Jones
'*syntax SeperateName(MyText,intIndicator)
'************************************************* *******

Function SeperateName(strMytext, intIndicator) As String 'Exit the function when MyText and Indicator no specified
If IsNull(strMytext) Then
Exit Function
End If
If IsNull(intIndicator) Then
Exit Function
End If

intCounter = Len(strMytext) 'Length of the name

For MyNumber = 1 To intCounter ' Scanning for space breaks the loop and the character index is recorded in intBreak
MyAlpha = Right$(Left$(strMytext, MyNumber), 1)
If MyAlpha = " " Then Exit For
Next MyNumber
intBreak = MyNumber
str1stName = Left$(strMytext, intBreak)


Select Case intBreak 'Scanning for 2nd name only if intBreak is less than the Len(Mytext)
Case Is < intCounter
intStarter = intBreak + 1
For MyNumber = intStarter To intCounter
MyAlpha = Right$(Left$(strMytext, MyNumber), 1)
If MyAlpha = " " Then Exit For
Next MyNumber
intBreak = MyNumber
str2ndName = Mid$(strMytext, intStarter, (intBreak - intStarter))
Case Else
str2ndName = ""
End Select

Select Case intBreak
Case Is < intCounter
intStarter = intBreak + 1
For MyNumber = intStarter To intCounter
MyAlpha = Right$(Left$(strMytext, MyNumber), 1)
If MyAlpha = " " Then Exit For
Next MyNumber
intBreak = MyNumber
str3rdName = Mid$(strMytext, intStarter, (intBreak - intStarter))
Case Else
str3rdName = ""
End Select

Select Case intBreak
Case Is < intCounter
intStarter = intBreak + 1
For MyNumber = intStarter To intCounter
MyAlpha = Right$(Left$(strMytext, MyNumber), 1)
If MyAlpha = " " Then Exit For
Next MyNumber
intBreak = MyNumber
str4thName = Mid$(strMytext, intStarter, (intBreak - intStarter))
Case Else
str4thName = ""
End Select


Select Case intIndicator 'The indicator specifies which names to select.
Case Is = 1
SeperateName = str1stName
Case Is = 2
SeperateName = str2ndName
Case Is = 3
SeperateName = str3rdName
Case Is = 4
SeperateName = str4thName

End Select
End Function


if this solves yur problem mark the thread solved:

attaching a sample mdb file for reference.

Check out Table1 Query to see the effect.

[maximus]

Have you been able to solve your problem. Did my post help you any way if yes mark the thread solved.