Solved

Access 2010 Split Full Name

Posted on 2014-12-31
15
124 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now