Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

Access 2010 Split Full Name

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
shieldsco
Asked:
shieldsco
  • 6
  • 5
  • 2
  • +2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
hnasrCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Rey Obrero (Capricorn1)Commented:
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
 
Helen FeddemaCommented:
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
 
shieldscoAuthor Commented:
None of the suggestions seem to take into consideration suffixes. Any thoughts
0
 
Rey Obrero (Capricorn1)Commented:
<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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
shieldscoAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
shieldscoAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
if you are passing "Mike Jones Jr"

use this

ExtractLastName = Split(AnyName, " ")(1)
0
 
shieldscoAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
you mean two spaces?

change this

AnyName = Trim(AnyName)

with

AnyName = Trim(Replace(AnyName,space(2),space(1)))
0
 
shieldscoAuthor Commented:
Thanks - You did exactly what I asked for.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now