?
Solved

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

Posted on 2014-03-13
9
Medium Priority
?
2,668 Views
Last Modified: 2016-03-20
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
Comment
Question by:gtmathewDallas
[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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 39927552
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
 

Author Comment

by:gtmathewDallas
ID: 39927865
Thank you etechO
0
 
LVL 10

Accepted Solution

by:
etech0 earned 2000 total points
ID: 39928160
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Closing Comment

by:gtmathewDallas
ID: 39929319
Excellent etech0, Thank you so much
0
 
LVL 10

Expert Comment

by:etech0
ID: 39929352
My pleasure!
0
 

Expert Comment

by:Nancy Carpenter
ID: 40204257
I get #Func! when I try to run this query.  I am not sure how to fix this.  Can you help?

Thanks!
0
 

Expert Comment

by:Nancy Carpenter
ID: 40204272
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
 

Expert Comment

by:Nancy Carpenter
ID: 40204285
The other problem is that some of the MiddleName fields have both middle name and last name with 2 spaces in between.
0
 
LVL 1

Expert Comment

by:Mswetsky
ID: 41515171
was there a solution to this #Func!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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…
Suggested Courses

777 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