Solved Parse Name Parts?

[theKruser]

I have a database output that I am trying to import into my database. I do not have access to the other database to create my own output. The database outputs name in format Last, First MI (there are two spaces between the comma and the first name(no idea why) and always a trailing space after the first name, regardless of presence of middle initial). Here is what I have so far.

Last name works just fine, so does middle initial. I don't know what I am doing wrong with the first name. It is the same code as last name used on a different variable.

Ends state, I just need to extract the names separately. If I am way off, or if there is a better way of doing it, I would love to learn what I am doing wrong.

Thank you in advance for taking the time to help me.

Public Function SplitName(strPart As String, strFullName As String) As String
    On Error GoTo ErrorHandler
    
'Etracts names from a field with full name

    Dim strNamePart As String
    Dim strF As String
    Dim strM As String
    Dim strFM As String
    
    strFM = Mid(strFullName, InStr(strFullName, "  ") + 2) 'extracts everything right of the comma
    
    strM =
    
    Select Case strPart
        Case "F"
            strNamePart = Left(strFM, InStr(strFM, " ") - 1)
        Case "M"
            strNamePart = Right(strFullName, 1)
        Case "L"
            strNamePart = Left(strFullName, InStr(strFullName, ",") - 1)
    End Select
            
    SplitName = Trim(StrConv(strNamePart, 3))
  
ExitProcedure:
    On Error Resume Next
    Exit Function

ErrorHandler:
    Call ErrHandler("basTools", 0, Err.Number, Err.Description)
    Resume ExitProcedure

End Function

 

[theKruser]

UPDATE: Middle initial does not work fine. In the absence of a middle initial in the field, it grabs the last letter of the first name.

 

[Isaac]

Have you tried compiling your code? That code won't even compile, for several reasons

 

[theKruser]

Have you tried compiling your code? That code won't even compile, for several reasons

Yes, I have compiled it. I forgot to delete the strM = line, however. I corrected that in my code. It won't compile for you because ErrHandler is a custom function that I wrote years ago that works with my databases specifically. If you comment out the Call ErrorHandler line, it should compile fine.

 

[theDBguy]

Hi. There has to be a sample already available somewhere, but your function's name should give you a clue as to how to get better results.

Hint: Rather than use Left/Mid/Right, try using the Split() function.

 

[Isaac]

Yes, I have compiled it. I forgot to delete the strM = line, however. I corrected that in my code. It won't compile for you because ErrHandler is a custom function that I wrote years ago that works with my databases specifically. If you comment out the Call ErrorHandler line, it should compile fine.

Okay. Just wanted to be sure you knew and/or weren't using it uncompiled.

dbGuy made a great suggestion, I would highly recommend it. Looping through the resulting array will then be a breeze.

 

[theKruser]

Okay. Just wanted to be sure you knew and/or weren't using it uncompiled.

dbGuy made a great suggestion, I would highly recommend it. Looping through the resulting array will then be a breeze.

That makes sense. Thank you for your help!

 

[theKruser]

Hi. There has to be a sample already available somewhere, but your function's name should give you a clue as to how to get better results.

Hint: Rather than use Left/Mid/Right, try using the Split() function.

Thank you for the suggestion. I toyed around with it, but couldn't figure the Split() function out rapidly and I am under a timeline. For some reason, my original code listed above now works. I have no idea how or why, but it does. Thank you for your time and suggestion! When time permits, I will try to figure out the Split() function, as it does seem the more "right" way to do it.

 

[theDBguy]

Thank you for the suggestion. I toyed around with it, but couldn't figure the Split() function out rapidly and I am under a timeline. For some reason, my original code listed above now works. I have no idea how or why, but it does. Thank you for your time and suggestion! When time permits, I will try to figure out the Split() function, as it does seem the more "right" way to do it.

Hi. Glad to hear you got it working. Like I said, there has to be plenty of examples already available. But, I just quickly put together the following for you. Hope it helps...

Public Function GetNamePart(FullName As String, Optional WhichPart As String = "L") As String
'thedbguy@gmail.com
'4/1/2021
'assumes following input format: Lastname, Firstname Middlename
'usage: GetNamePart([FieldName], "m")

Dim strName() As String
Dim strNamePart As String

Select Case WhichPart
    Case "L" 'Lastname
        strName = Split(FullName, ",")
        strNamePart = strName(0)
    
    Case "F" 'Firstname
        strName = Split(Trim(Split(FullName, ",")(1)), " ")
        strNamePart = strName(0)
    
    Case "M" 'Middlename
        strName = Split(Trim(Split(FullName, ",")(1)), " ")
        strNamePart = strName(UBound(strName))
    
End Select

GetNamePart = Trim(strNamePart)

End Function

 

[Isaac]

Glad you got something working and now an example too