[AccessD] Splitting up names

John,

    Functions  Fn_FirstName() & Fn_LastName() as given below, should get you the desired results as per pattern indicated by you..

    Just make sure that the string variable named SuffixList in Fn_FirstName() includes all the likely suffixes.

    Could you please try it out and let me know whether any further fine tuning is needed.

Best wishes,
A.D.Tejpal
----------------

===========================================
Function Fn_FirstName(ByVal FullName As _
                                                    String) As String
    ' Returns First Name
    Dim Txt As String, Cnt As Long
    Dim SuffixList As String, Rtv As Variant
    
    SuffixList = "Sr-Jr-Dr-Esq-Rev-Hon-Sir-Lord"
    
    Txt = FullName
    
    Rtv = Split(SuffixList, "-")
    For Cnt = 0 To UBound(Rtv)
        Txt = Trim(Replace(Txt, " " & Rtv(Cnt), ""))
    Next
    
    Fn_FirstName = Trim(Left(Txt, InStrRev(Txt, " ") - 1))
    
End Function
------------------------------------------------------------------------------

Function Fn_LastName(ByVal FullName As _
                                                    String) As String
    ' Returns Last Name
    Dim Txt As String
    
    Txt = Fn_FirstName(FullName)
    
    Fn_LastName = Trim(Mid(FullName, Len(Txt) + 1))
    
End Function
===========================================

  ----- Original Message ----- 
  From: John Clark 
  To: accessd at databaseadvisors.com 
  Sent: Thursday, May 11, 2006 23:51
  Subject: [AccessD] Splitting up names


  If got a user running a db in Access. She has a list of names and they
  were all input into a single "name" field, rather than "last name" &
  "first name." She would now like to split these names up, but there are
  some double first names, as well as "jr" and "sr" suffixes. 

  Is there a way to do this?

  Basically names might be like this:

  JOHN SMITH JR
  MARY ELLEN SMITH
  BARBARA SMITH

  And I'd need to get them like this into a first name field like:

  JOHN
  MARY ELLEN
  BARBARA

  and a last name field like:

  SMITH JR
  SMITH
  SMITH

  John W. Clark
  Computer Programmer
  Niagara County
  Central Data Processing