split a fullname with title

[Rosana]

HI,
I am writing a query which split fullname field into separate columns(firstname,middlename,lastname,title or suffix).
Here is my query so far and it seems to be working fine except for the ones with title or suffixes.

Query:
---------

SELECT Participants.[Full Name], GetNamePortion([Full Name],1) AS FirstName, GetNamePortion([Full Name],2) AS MiddleName, GetNamePortion([Full Name],3) AS LastName, GetNamePortion([Full Name],4) AS Suffix
FROM Participants;

Module:
----------

Function GetNamePortion(NameStr As String, PortionNum As Long) As String
     Dim arr As Variant
     Dim arr2 As Variant
     Dim arr3 As Variant
     On Error Resume Next
     arr = Split(NameStr, ", ")
     arr2 = Split(arr(1), " ")
     arr3 = Split(arr(0), " ")
     Select Case PortionNum
         Case 1: GetNamePortion = arr2(0) 'first name
         Case 2: GetNamePortion = arr2(1) 'middle name
         Case 3: GetNamePortion = arr3(0) 'last name
        Case 4: GetNamePortion = arr3(1) 'suffix, like Jr or III
     End Select
 End Function

For ex: Aquilio, III, John gives me results like this:
firstname - III
Middlename -
Lastname - Aquilio
Suffix -

I am expecting a result like this:
firstname - John
Middlename -
Lastname - Aquilio
Suffix - III

Thanks for any help.

 

[plog]

Read up on Split (https://www.techonthenet.com/access/functions/string/split.php). You only need to do it once, if your NameStr is formatted like you say it is.

Walk through your code, set break points if necessary so you can see what each variable contains at each step:

NameStr = "Aquilo, III, John"

arr = Split(NameStr, ", ")
    ' arr = {"Aquilo", "III", "John"}

arr2 = Split(arr(1), " ")
    ' arr(1) = "III"
    ' arr2 = {"III"}

arr3 = Split(arr(0), " ")
    ' arr(0) = "Aquilo"
    ' arr3 = {"Aquilo"}


So...

arr2(0) = "III"
arr2(1) = NULL
arr3(0) = "Aquilo"
arr3(1) = Null

With all that said, are you sure everything is formatted like that? What happens when someone doesn't have a suffix? Or James Van Der Beek shows up? Or George Herbert Walker Bush?

 

[jdraw]

Further to plog's comments:

Be prepared for
-records with one or more of the components missing
-names with apostrophe/quote such as O'Halloran, O'Leary
-names with complexities such as Oscar de la Hoya, Martanne des Champs-Elysées, Ali vant Goor

Get some good test data and test your conditions before getting too much volume.

 

[arnelgp]

Public Function GetNamePortion(ByVal field As Variant, ByVal Position As Byte) As String
    Static strName As String
    Static arrNames(1 To 4) As String
    Dim i As Byte
    Dim var As Variant
    Dim v As Variant
    Const Prefix As String = "/I/II/II/IV//V/VI/VII/VIII/VIX/VX/"
    
    ' convet to string
    field = field & ""
    If Trim(field) = "" Then Exit Function
    If Position > 4 Then Exit Function
    If strName <> field Then
        strName = field
        ' initialise array
        arrNames(1) = ""
        arrNames(2) = ""
        arrNames(3) = ""
        arrNames(4) = ""
        
        var = Split(field, ",")
        For i = 0 To 3
            If i > UBound(var) Then Exit For
            v = Trim(var(i))
            If InStr(Prefix, "/" & v & "/") > 0 Then
                arrNames(4) = v
            Else
                If i = 0 Then
                    arrNames(1) = v
                ElseIf arrNames(4) <> "" Then
                    arrNames(i) = v
                Else
                    arrNames(i + 1) = v
                    
                End If
            End If
        Next
    End If
    Select Case Position
        Case Is = 1
            GetNamePortion = arrNames(2)
        Case Is = 2
            GetNamePortion = arrNames(3)
        Case Is = 3
            GetNamePortion = arrNames(1)
        Case Is = 4
            GetNamePortion = arrNames(4)
    End Select
End Function