INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Howto:

How Can I parse a Full Name into Last, First, MI when I receive it different ways? by jimmythegeek
Posted: 26 May 00 (Edited 27 Sep 07)

The following function works great, I have used it many times. You can receive the name spelled nine different ways, and this will parse it.

Just copy the whole thing into a new module, replace the bolded area's, open the immediate window and type "Call ParseName"

Hope this helps



Function ParseName()
'This function breaks apart a single name field into seperate first, last  & middle initial fields.
'This will work for a name entered in any of the following ways:
' {Smith, John}  {Smith, John D}  {Smith, John D.}
' {Smith,John}   {Smith,John D}   {Smith,John D.}
' {Smith John}   {Smith John D}   {Smith John D.}
                              
'Start by adding 3 fields to the table where your single name field is (firstname, lastname, & MI)
                     
On Error GoTo Parse_Err
         
    Dim db As Database
    Dim rs As Recordset
    Dim fldName As Field
    Dim x As Integer
    Dim strLast As String, strFirst As String, strMI As String
                              
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("TableNameHere", dbOpenDynaset)           'open appropriate table
    Set fldName = rs![NameFieldHere]                                                'single name field
                              
    DoCmd.Hourglass True
    Do Until rs.EOF
        If IsNull(rs!FirstName) Then                  
            x = InStr(1, fldName, ",")                 
            If x = 0 Then                                   
                x = InStr(1, fldName, " ")              
                strLast = Left(fldName, x - 1)        
                strFirst = Mid(fldName, x + 1)       
                If Right(strFirst, 1) = "." Then       
                    strMI = Right(strFirst, 2)          
                    strFirst = Left(strFirst, Len(strFirst) - 3)    
                Else
                    If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then   
                        strMI = Right(strFirst, 1)
                        strFirst = Left(strFirst, Len(strFirst) - 2)
                    Else
                        strMI = ""
                        strFirst = strFirst
                    End If
                End If
            Else
                If Mid(fldName, x + 1, 1) = Chr(32) Then
                    strLast = Left(fldName, x - 1)
                    strFirst = Mid(fldName, x + 2)
                    If Right(strFirst, 1) = "." Then
                        strMI = Right(strFirst, 2)
                        strFirst = Left(strFirst, Len(strFirst) - 3)
                    Else
                        If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then
                            strMI = Right(strFirst, 1)
                            strFirst = Left(strFirst, Len(strFirst) - 2)
                        Else
                            strMI = ""
                            strFirst = strFirst
                        End If
                    End If
                Else
                    strLast = Left(fldName, x - 1)
                    strFirst = Mid(fldName, x + 1)
                        If Right(strFirst, 1) = "." Then
                            strMI = Right(strFirst, 2)
                            strFirst = Left(strFirst, Len(strFirst) - 3)
                        Else
                            If Mid(strFirst, Len(strFirst) - 1, 1) = Chr(32) Then
                                strMI = Right(strFirst, 1)
                                strFirst = Left(strFirst, Len(strFirst) - 2)
                            Else
                                strMI = ""
                                strFirst = strFirst
                            End If
                        End If
                    End If
                End If
                  
                With rs
                    .Edit
                    !LastName = strLast             
                    !FirstName = strFirst
                    !MI = strMI
                    .update
                    .MoveNext
                End With
            Else
                rs.MoveNext
            End If
        Loop
        DoCmd.Hourglass False
                                   
        rs.CLOSE
        db.CLOSE

Parse_Exit:
    Exit Sub

Parse_Err:
    DoCmd.Hourglass False
    MsgBox Err.Number & ": " & ErrDescription
    Resume Parse_Exit                              
End Function

==========================

Jim Lunde
jimlunde@gmail.com

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close