Solved

Access 2010 Split Full Name

Posted on 2014-12-31
15
142 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

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.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

710 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