Improve company productivity with a Business Account.Sign Up

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

Separate First Name - Middle Name - Last Name to three columns - ACCESS VBA SQL

I have a table field of people names(full name) in an order of "First Name, Last Name Middle Name", I need to separate this field records to three separate columns like First Name Middle Name and Last Name. Can you please help me experts?

Sample Data
==========
Mayes,Taryn
McAdams,Carrie
McCandless,Rosemary Van Linde
McConkey,Morgan E
McCurdy,Jeanne C
0
gtmathewDallas
Asked:
gtmathewDallas
  • 3
  • 3
  • 2
  • +1
1 Solution
 
etech0Commented:
Sounds like you'd need to use a combination of InStr, Left, Right, Mid, and Len functions. I can put it together for you later if you want, when I have access to a computer with Access.
0
 
gtmathewDallasAuthor Commented:
Thank you etechO
0
 
etech0Commented:
Okay, here goes:

(Make a query with this SQL)
SELECT Table1.PersonsName, InStr([PersonsName],",") AS comma, Left([PersonsName],[comma]-1) AS LastName, IIf(InStr([comma],[personsname]," ")=0,Len([personsname])+1,InStr([comma],[personsname]," ")) AS FirstSpace, Mid([PersonsName],[comma]+1,[firstspace]-[comma]-1) AS FirstName, Mid([personsname],[firstspace]+1,Len([personsname])) AS MiddleName
FROM Table1;

Open in new window


Notes: Replace "Table1" with the name of the table we're looking at, and replace "PersonsName" with the name of the field with the name in it. The rest should stay the same. There will be a couple of columns in the query that you can ignore (they are used for calculations).

Let's see if this works!
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
gtmathewDallasAuthor Commented:
Excellent etech0, Thank you so much
0
 
etech0Commented:
My pleasure!
0
 
Nancy CarpenterPresidentCommented:
I get #Func! when I try to run this query.  I am not sure how to fix this.  Can you help?

Thanks!
0
 
Nancy CarpenterPresidentCommented:
I think I get the #Func! error in the LastName field because it is returning a null.  All my last names are in the MiddleName field.
0
 
Nancy CarpenterPresidentCommented:
The other problem is that some of the MiddleName fields have both middle name and last name with 2 spaces in between.
0
 
Mitch SwetskyBusiness AnalystCommented:
was there a solution to this #Func!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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