Solved

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

Posted on 2014-03-13
9
2,507 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
  • 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 500 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
 

Author Closing Comment

by:gtmathewDallas
ID: 39929319
Excellent etech0, Thank you so much
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

20 Experts available now in Live!

Get 1:1 Help Now