Access split full name field

shieldsco
shieldsco used Ask the Experts™
on
I'm trying to split a full name field LastName, FirstName or LastName, FirstName Initial or LastName, FirstName Initial. (period). into FirstName and LastName fields, If there is initial I want to disregard.
Examples:
Smith, John
Smith, John A
Smith, John A.
Smith, John JR
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Public Function SplitInput()
Dim i As Integer
Dim ss As String
Dim splitted As Variant

ss = "Smith, John JR"
splitted = Split(ss, " ")
For i = LBound(splitted) To UBound(splitted)
    Debug.Print splitted(i)
Next
End Function

Open in new window

Author

Commented:
I'm trying to split a field not just one name
John TsioumprisSoftware & Systems Engineer

Commented:
Public Function SplitInput(InputField as string)
Dim i As Integer
Dim splitted As Variant
splitted = Split(InputField , " ")
For i = LBound(splitted) To UBound(splitted)
    Debug.Print splitted(i)
Next
'Here i don't know how you are going to use it ...
End Function

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Paul Cook-GilesSenior Application Developer

Commented:
Assuming your FullName field is consistently populated with the LastName first, followed by a comma, you can use this functionality:

Create a public function to return the portion of a string before a specified character:
Public Function BitsBeforeFirst(strString As Variant, strFirstChars As String) As String
'returns the portion of strString before the first occurence of strFirstChars.
If strString = "" Then
   BitsBeforeFirst = ""
   Exit Function
End If
Dim intFirstChars As Integer
   intFirstChars = InStr(strString, strFirstChars)
   BitsBeforeFirst = Left(strString, intFirstChars - 1)
End Function

Open in new window


Then create a function that can splits the FullName value into first and last names:
Public Function SplitFullName(strFullNa As String)
Dim strFirstNa As String, strLastNa As String

strLastNa = BitsBeforeFirst(strFullNa, ",")
strFirstNa = Replace(strFullNa, strLastNa & ",", "")

Debug.Print "First:  " & strFirstNa & ";  Last: " & strLastNa
End Function

Open in new window


Once you're confident that the function is correctly splitting FullName, you can modify it to update your table:
Public Function SplitFullName(strFullNa As String, intPK As Integer)
Dim strFirstNa As String, strLastNa As String

strLastNa = BitsBeforeFirst(strFullNa, ",")
strFirstNa = Replace(strFullNa, strLastNa & ",", "")

DoCmd.SetWarnings False
    DoCmd.RunSQL "Update YourTableNa set FirstName = '" & strFirstNa & "', LastName = '" & strLastNa & "' where YourPrimaryKey = " & intPK
DoCmd.SetWarnings True

End Function

Open in new window


This assumes that your table has a Primary Key that is an autonumber;  if it doesn't you'll need to tweak the function accordingly.
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Here's a good little VBA user defined function you could use in Access to parse apart full names.  It may need a little tweaking to handle your specific needs, but it should give you a good starting point and handles multi component names with initials, suffixes, etc.


Function ParseOutNames(FullName As String) As Variant

Dim FirstName As String
Dim LastName As String
Dim MidInitial As String
Dim Suffix As String
Dim Pos As Integer
Dim Pos2 As Integer
Dim Pos3 As Integer

Pos = InStr(1, FullName, ",", vbTextCompare)
If Pos = 0 Then
    Pos = Len(FullName) + 1
End If
LastName = Trim(Left(FullName, Pos - 1))

Pos2 = InStr(1, LastName, " ", vbTextCompare)
If Pos2 Then
    Pos3 = InStr(Pos2 + 1, LastName, " ", vbTextCompare)
    If Pos3 Then
        Suffix = Right(LastName, Len(LastName) - Pos3)
        LastName = Left(LastName, Pos3 - 1)
    Else
        Suffix = Right(LastName, Len(LastName) - Pos2)
        LastName = Left(LastName, Pos2 - 1)
    End If
End If

Pos2 = InStr(Pos + 2, FullName, " ", vbTextCompare)
If Pos2 = 0 Then
    Pos2 = Len(FullName)
End If

If Pos2 > Pos Then
    FirstName = Mid(FullName, Pos + 1, Pos2 - Pos)
    MidInitial = Right(FullName, Len(FullName) - Pos2)
End If

Pos = InStr(1, LastName, "-", vbTextCompare)
If Pos Then
    LastName = Trim(StrConv(Left(LastName, Pos), vbProperCase)) & _
    Trim(StrConv(Right(LastName, Len(LastName) - Pos), vbProperCase))
Else
    LastName = Trim(StrConv(LastName, vbProperCase))
End If

FirstName = Trim(StrConv(FirstName, vbProperCase))
MidInitial = Trim(StrConv(MidInitial, vbProperCase))
Suffix = Trim(StrConv(Suffix, vbProperCase))
'
' suffix handling
'
Select Case UCase(Suffix)
    Case "JR", "SR", "II", "III", "IV", "MD", "PHD", "PH.D", "M.D."

    Case Else
        If Not IsNumeric(Left(Suffix, 1)) Then
            LastName = LastName & " " & Suffix
            Suffix = ""
        End If
End Select

ParseOutNames = Array(LastName, FirstName, MidInitial, Suffix)

End Function

Open in new window


»bp

Author

Commented:
Paul -error message

Error

SplitFullName([Team Member],[ID])
John TsioumprisSoftware & Systems Engineer

Commented:
My Solution works...have you tested it...?
If you don't want the initials you just don't use the last element of the array

Author

Commented:
John - get blank records from a query


SELECT SplitInput([FullName]) AS LastName
FROM tblAssignment;

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
Can you give  a couple of data ?
John TsioumprisSoftware & Systems Engineer

Commented:
Please note that the function i posted doesn't return anything...i left it for you to decide how to handle it
If you want something like the above
Public Function SplitInput(InputField as string,IndexNo as Integer) As String
Dim i As Integer
Dim splitted As Variant
splitted = Split(InputField , " ")
SplitInput = splitted(IndexNo)
End Function

Open in new window

So this should work
SELECT SplitInput([FullName],0) AS LastName
FROM tblAssignment;

Author

Commented:

Author

Commented:
Subscript out of range

SplitInput = splitted(IndexNo)
Software & Systems Engineer
Commented:
Just a small (cosmetic) fix...it works fine just test it
Public Function SplitInput(InputField As String, IndexNo As Integer) As String
Dim i As Integer
Dim splitted As Variant
splitted = Split(InputField, " ")
SplitInput = Replace(splitted(IndexNo), ",", "")
End Function

Open in new window

Clipboard01.jpg

Author

Commented:
How do I get first name . Worked for last name

Author

Commented:
Got it thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial