[AccessD] Splitting up names

Sorry that it has taken so long to get back to y'all...been extremely
busy, and every time I sit down to do something, something else comes
up.

As usual I've gotten some good ideas from you all. As I suspected,
there doesn't seem to be a clear-cut solution. I picked up a little from
many of these posts, and I have actually begun some code that is
progressing nicely toward a solution.

I haven't tried this code here yet...I'll try to get to it in a little
bit...but I'm sure it too will help me out.

This project isn't exactly high priority...it is basically helping a
former co-worker that has moved on...so it has been difficult to put
much time into it, with everything else going on.

Thanks again though!

John W. Clark


>>> adtp at hotmail.com 5/12/2006 1:01 am >>>
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
-- 
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com