Reversing SOME Names 1

[DonP]

An archived post has a function that seems to do what I need, which is to reverse and comma-separate names that are in a single field, except that in this case, some of the names have already been separated so do not need it again. Also, it seems to have a bug as it crashes on the line marked in red. Any ideas or does someone have a better, more up to date function? Thanks.

Function ParseName(pFullName As String) As String
'**************************************************
'Purpose:   Converts full name (e.g. Elmer T. Fudd)
'           to lname, fname, mname format
'Coded by:  raskew
'Calls:     xLastInStr()
'Inputs:    ? ParseName("Elmer T. Fudd")
'Output:    Fudd, Elmer T.
'Note:      As written, this function will correctly
'           return no / multiple / initial only / no
'           middle name(s) or initials.
'           It will not, however, deal with prefix
'           (e.g. Dr., Rev., etc) or suffix (e.g.
'           Jr., Sr., III, etc.)
'**************************************************

Dim intF As Integer
Dim intL As Integer
Dim strHold As String
Dim strKeep As String

   strHold = pFullName
   intF = InStr(strHold, " ")
   intL = xLastInStr(strHold, " ")
   [COLOR=red]strKeep = Trim(Mid(strHold, intL)) & ", " & Trim(Left(strHold, intF))[/color]
   strKeep = strKeep & IIf(intF <> intL, RTrim(Mid(strHold, intF, intL - intF)), "")
   ParseName = strKeep
   
End Function

Function xLastInStr(pStr As String, _
                    pDelim As String) As Integer

Dim i    As Integer
Dim n    As Integer
Dim tlen As Integer

    n = 0
    tlen = Len(pDelim)
    For i = Len(RTrim(pStr)) To 1 Step -1
    
      If Mid(pStr, i, tlen) = pDelim Then
          n = i
          Exit For
      End If
    Next i
    
    xLastInStr = n

End Function

Don

http://www.pc-homepage.com/

http://www.personalzone.net/

Experienced in HTML, Perl, PHP, VBScript, PWS, IIS and Apache and MS-Access, MS-SQL, MySQL databases

 

[PHV]

Something like this (ac2k or above):
Function ParseName(pFullName As String) As String
Dim intF As Integer
Dim intL As Integer
Dim strHold As String
Dim strKeep As String
strHold = Trim(pFullName)
intF = InStr(strHold, " ")
If InStr(pFullName, ", ") > 0 Or intF = 0 Then
ParseName = pFullName
Exit Function
End If
intL = InStrRev(strHold, " ")
strKeep = Trim(Mid(strHold, intL)) & ", " & Trim(Left(strHold, intF))
If intF < intL Then
strKeep = strKeep & RTrim(Mid(strHold, intF, intL - intF))
End If
ParseName = strKeep
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DonP]

Thanks, it works perfectly!

Don

http://www.pc-homepage.com/

http://www.personalzone.net/

Experienced in HTML, Perl, PHP, VBScript, PWS, IIS and Apache and MS-Access, MS-SQL, MySQL databases