Access 2016 - query

Is the name(s) in the filed like:

Bob Smith                            each name in separate row
Jim Governor

or, all names in the same row
Bob Smith Jim Governor    

Could you paste a few rows of data for me to create a test table?

Left(MyName, InStr(MyName, " ")  -- will get you the first "word"

Right(MyName, InStrRev(MyName, " ") +1)  -- will get you the last "word"

Keep in mind that there is a large variation in names and mushing the various parts using space as the separator will result in less than optimal results any time the first or last name is two words or there is a prefix or suffix.  For best results, NEVER, EVER store names this way.

Ann Marie St Martin  -- Ann Marie is the first name and St Martin is the last name but the above functions will pull out Ann and Martin which will both be wrong.

John Jones Jr  -- will select Jr as the last name.

Dr Susan Rush -- will select Dr as the first name.

You may use a split function: funReturn (textValue as String)
 to split the text to array arr and return a value:

funReturn = arr(2) & " " & arr(0)

first middle last ==> last first

In query:
Select funReturn(textName) As theName

I made some sample data as shown below.

SELECT tblNames.ID, tblNames.FullName, Left([FullName],InStr([FullName]," ")) AS FName, Mid([FullName],InStr([FullName]," "),InStrRev([FullName]," ")-4) AS MName, Mid([FullName],InStrRev([FullName]," ")+1) AS LName
FROM tblNames;

Select allOpen in new window

The above solution depends on the first blank space from the left and the first blank space from the right. If your data differs, please post the variations.

Mike

In response to Mike, I tried your suggestion, but it looks like I got into trouble, and I think u would be able to spot the error easily.  The name of the table is : tblJasonJustin and the name of the ID field is Numb, and the name of the field where the name is : NameonCard.

Based on your example, I tried the following but got a syntax error:
SELECT tblJasonJustin.Numb, tblJasonJustin.NameonCard,
Left([NameonCard,InStr([NameonCard]," ") AS FName,
Mid([NameonCard],InStr([NameonCard]," "),InStrRev([NameonCard]," ")-4) AS MName,
Mid([NameonCard],InStrRev((NameonCard]," ")+1) AS LName
FROM tblJasonJustin;

Hope u can please spot the error or maybe it Is a typo.   Thank u and regards.

in reponse to Pat, thank u for your input.  Hope u can please show me the syntax to extract the middle name.  thank u and regards

in response to Pat, I inherited this data, thus my effort to clean it up and keep it clean. Thank u for your suggestion to keep the names separate, it helps for the User to be accurate when entering data. thank u.

This should correct the syntax errors you are having.

SELECT tblJasonJustin.Numb, tblJasonJustin.NameonCard,
Left([NameonCard],InStr([NameonCard]," ")) AS FName,
Mid([NameonCard],InStr([NameonCard]," "),InStrRev([NameonCard]," ")-InStr([NameonCard]," ")) AS MName,
Mid([NameonCard],InStrRev([NameonCard]," ")+1) AS LName
FROM tblJasonJustin;

Select allOpen in new window

~bp

If your intention is to clean up the data and store it separated correctly, then the best solution is to use the Split() function in a VBA procedure to separate all the parts.  You would not do this with a single update query. Here is an outline of the procedure I would code.
1. Replace all periods and commas with spaces
2. Use the Split() function to separate the words
3. Create an array of prefixes
4. Determine if the first name is a prefix.  If it is a prefix, store it in the prefix field and store the second word as the firstName, otherwise store it in the FirstName field
5. If the first word is not a prefix, store it in the FirstName field
6. Create an array of suffixes
7.  If the last word is a suffix, store it in the suffix field and store the next to last word as the lastName otherwise, store it in the LastName field
8.  Store the remaining words in the middle name field

Now comes the manual work.  Anything with a middle name needs to be evaluated to determine of part of the last name ended up being stuffed into the middle name.  It is harder to identify two-part first names and I don't usually worry about that.

Once all the data has been parsed, using separate fields will keep it clean.  Use combos for the prefix and suffix fields so people don't accidentally use them for names.

The submitted solution is tested and it works as shown above. Below, I have changed the table and column names what you already have. The first thing I have asked was some sample data (which generally includes table and column names you are working).

SELECT T.Numb, T.NameonCard, Left([NameonCard],InStr([NameonCard]," ")) AS FName, Mid([NameonCard],InStr([NameonCard]," "),InStrRev([NameonCard]," ")-4) AS MName, Mid([NameonCard],InStrRev([NameonCard]," ")+1) AS LName
FROM tblJasonJustin T ;

Select allOpen in new window

In my sample database, I will plug the new table name and column names and post the result for you.

Mike

Here is the tested result with the new table and column names you provided:
                                            --- Directly from my test db in Access ---

SELECT tblJasonJustin.Numb, tblJasonJustin.NameonCard, Left([NameonCard],InStr([NameonCard]," ")) AS FName, Mid([NameonCard],InStr([NameonCard]," "),InStrRev([NameonCard]," ")-4) AS MName, Mid([NameonCard],InStrRev([NameonCard]," ")+1) AS LName
FROM tblJasonJustin;

Select allOpen in new window

in response to Mike, sorry it is my fault, but I did not not mention that some of the data only had first and last name and did not have a middle name.  So your query does not work for only first and last name in the NameonCard field.
Thank u and regards

re: So your query does not work for only first and last name in

The first sample data "Mike Eghtebas", as you can see above, works very well. Do you want me to upload the test database for you to see it for yourself?

Mike

Could you please paste your version of SQL and its printout (the image of it as I have above). Also, do you have some other configuration of the names I have not included in my test sample names? If you do, please tell us what other combinations you have.

Or maybe to upload a minimized version of your database.

Thanks

Mike

In response to Mike, thank u.  Here is the SQL statement that I have :
SELECT tblJasonJustin.Numb, tblJasonJustin.NameonCard,
([NameonCard],InStr([NameonCard]," ")) AS FName,
Mid([NameonCard],InStr([NameonCard]," "),InStrRev([NameonCard]," ")-InStr([NameonCard]," ")) AS MName,
Mid([NameonCard],InStrRev([NameonCard]," ")+1) AS LName
FROM tblJasonJustin;

Attached is the output result of the above query.
qrysplit-to-fname_-mname-and-lname.txt

Thank you for the response. I located three items (as shown below) to discuss them with you:

Starting with N0. 2:
As you can see above, Lines 2, 12, and 13 although have only first name and last name are parsed correctly.

Line No. 1:
Becuase there is an extra space before "CLIFFORD", it messes up the code I have. To handle this, I have used an alias name FN (stands for full name) and Trim() function to get rid of the leading etra splaces, The new query will be like (for my database):

SELECT tblJasonJustin.Numb, tblJasonJustin.NameonCard, Trim([NameonCard]) AS FN, Left([FN],InStr([FN]," ")) AS FName, Mid([FN],InStr([FN]," "),InStrRev([FN]," ")-4) AS MName, Mid([FN],InStrRev([FN]," ")+1) AS LName
FROM tblJasonJustin;

Select allOpen in new window

And here is what it will work for you:

SELECT tblJasonJustin.Numb, tblJasonJustin.NameonCard, Trim([NameonCard]) AS FN,
([FN],InStr([FN]," ")) AS FName, 
Mid([FN],InStr([FN]," "),InStrRev([FN]," ")-InStr([FN]," ")) AS MName, 
Mid([FN],InStrRev([FN]," ")+1) AS LName
FROM tblJasonJustin;

Select allOpen in new window

Shortly, I will revise it to handle suffix like Jr. (item 3) as well.

Mike


FYI: Select 'My' As A, 'Car' As B, A + B as Msg From Table1 will work in Access but not in SQL Server. Access will do "All at same time" processing. But in SQL Server this is not possible. If interested, I will explain later how it it done in SQL Server.

Question on lines 66 and 67:  Do you want Jr and Sr handled this way?

|      66 | Michael R. Vause Jr | Michael   |R.   |Vause  Jr   |
|      67 | Jack J. Cozzi, Sr   | Jack      | J.  |Cozzi, Sr   |

Select allOpen in new window

in response to Mike, thank u for your reponse.  I would like the way as in No. 67.  Thank u again for taking the time.

So, I need to add a (,) to 66 like:

|      66 | Michael R. Vause Jr | Michael   |R.   |Vause, Jr   |

Although its NameonCard doesn't have the comma?

Thank u Mike, both your codes work, and I have integrate it into my application.  Thank u for the time u have put into this Question, which is very much appreciated, well done, and thank u and keep up the good work.

Hi jegajothy,

I enjoyed working on this question. I look forward to help with any other question you may have later on.

Mike