Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2010 Split Full Name

Posted on 2014-12-31
15
Medium Priority
?
145 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 31

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 58
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 58
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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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