Solved

Access 2010 Split Full Name

Posted on 2014-12-31
15
136 Views
Last Modified: 2015-01-02
I'm using the function below to extract last names. I have a few names in the format Joe Smith Jr that I need to disregard the Jr. Any thoughts on how to modify the function. Thanks

 Public Function ExtractLastName(AnyName As String) As String

 Dim nSpace As Integer

 AnyName = Trim(AnyName)

 nSpace = InStrRev(AnyName, " ")

 'One word in string
 If nSpace = 0 Then
    ExtractLastName = AnyName
    Exit Function
 End If

 ExtractLastName = Mid(AnyName, nSpace + 1)

 End Function
0
Comment
Question by:shieldsco
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40526225
try this

Public Function ExtractLastName(AnyName As String) As String

  Dim nSpace As Integer

  AnyName = Trim(AnyName)

  nSpace = InStrRev(AnyName, " ")

  'One word in string
  If nSpace = 0 Then
     ExtractLastName = AnyName
     Exit Function
      
  End If

  ExtractLastName = Split(AnyName, " ")(1)

  End Function
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40526301
Try this function: pass name as string n

Private Function getName(n As String) As String
    Dim ar() As String
    ar = Split(n, " ")
    getName = ar(1)
End Function

Open in new window

0
 
LVL 57
ID: 40526503
I would use split, but what your going  to find quite quickly is just breaking it apart is only the first step.   For example

Mary Ann Smith

'Mary Ann' being the first name, and 'Smith' the last.   vs :

John Doe, jr

So first step is to break up into pieces, then try and figure out what each piece represents based on the number of pieces, and what's in each.

Jim.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40526650
my post is just a direct respond to the request of modifying the function to accommodate the presented problem.

it will not handle all possible scenarios of parsing name.

I once wrote a function that handles all possible scenarios of parsing names for a paying client, but I can't post the codes here....
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40527904
I have an old sample database with a set of queries that will do some of the work of splitting or concatenating names and addresses (as others have noted, no automatic parsing method will work for all names, especially if you have some Hispanic or Asian names in the mix).  Here is the link for downloading it:

http://www.helenfeddema.com/Files/code37.zip

And to avoid this type of problem, if at all possible, restructure the table to have a separate field for each name component (Prefix, FirstName, MiddleName, LastName and Suffix).
0
 

Author Comment

by:shieldsco
ID: 40528048
None of the suggestions seem to take into consideration suffixes. Any thoughts
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40528056
<None of the suggestions seem to take into consideration suffixes. Any thoughts >

in your OP, you only mentioned < Joe Smith Jr that I need to disregard the Jr>
0
 
LVL 57
ID: 40528075
<<None of the suggestions seem to take into consideration suffixes. Any thoughts >>

  As I indicated:

So first step is to break up into pieces, then try and figure out what each piece represents based on the number of pieces, and what's in each.

there is no cut and dry method to this.  It depends on your data and how it's formatted.  

 What I do is start simple taking the most obvious (ie.  two pieces is a first and last name).  Review the results, then add additional logic (i.e. if three pieces and the last piece is "jr" or  "sr", then it's first, last, and suffix).

and continue on.  At some point, you'll get down to the point where you can update the remaining ones by hand.

For example, you might think three pieces with no matching suffix is a "Mary Ann Smith", "Mary Ann" being the first name.   But what about "Mary Mc Donald"?

That may mean adding another rule; if piece #2 is "Mc", then it's first name and piece #2 and #3 is the last.  

But no one is going to be able to hand you a piece of code and have it work 100% for you.

Jim.
0
 

Author Comment

by:shieldsco
ID: 40528080
That correct, however when there is  Jr (suffix) the record is blank. For example
      Fname      Lname
      Maurilio      Gomez Jr

Fname      Lname      IPACDocumentReferenceNumber      TransactionType      DetailAmount
Maurilio            05134409      C      
Maurilio            05134418      C      
Maurilio            05134430      C      
Maurilio            05134432      C      
Maurilio            05134513      C      
Maurilio            05134514      P      
Maurilio            05134515      C      
Maurilio            05134516      P      
Maurilio            05134517      P
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40528091
which code are you using?

are the names already separated by FName, LName?

if you are passing the LName only to the function,

change this

ExtractLastName = Split(AnyName, " ")(1)

with

ExtractLastName = Split(AnyName, " ")(0)



.
0
 

Author Comment

by:shieldsco
ID: 40528126
The full name is in one field for example: Mike Jones Jr. When I changed the code to ExtractLastName = Split(AnyName, " ")(0)

Result are First Name and the Last Name fields are the same.

Fname      Lname      IPACDocumentReferenceNumber
Maurilio      Maurilio      05134516
Maurilio      Maurilio      05134513
Maurilio      Maurilio      05134514
Maurilio      Maurilio      05134515
Maurilio      Maurilio      05134517
Maurilio      Maurilio      05134432
Maurilio      Maurilio      05134430
Maurilio      Maurilio      05134418
Maurilio      Maurilio      05134409
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40528193
if you are passing "Mike Jones Jr"

use this

ExtractLastName = Split(AnyName, " ")(1)
0
 

Author Comment

by:shieldsco
ID: 40528252
Ok your solution works good however I have some records that the first and last names is separated by to spaces. Do I need to open up a new help ticket?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40528269
you mean two spaces?

change this

AnyName = Trim(AnyName)

with

AnyName = Trim(Replace(AnyName,space(2),space(1)))
0
 

Author Closing Comment

by:shieldsco
ID: 40528289
Thanks - You did exactly what I asked for.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question