In Access 2013, I need to seperate the first and last names in my imported data and create independent first and last name fields. It is formatted as "Jones,Tom"

I have been trying to use the InStr() function, at first just to find the comma, but it is returning this result:

My formula, so far is: InStr([Cust Name],[Cust Name],",",-1)

I understand there will be additional functions to define what to do with the data after the position of the comma is determined, but have had no luck in getting this right.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
name_Last = LEFT([Cust Name], instr([Cust Name], ",") -1)
name_First = TRIM(MID([Cust Name], instr([Cust Name], ",") + 1))
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Just to add a bit (no points please), Instr() just returns a position within the string you searched.  You have to take that and then use it with other functions like Left(), mid(), etc to pull out the string you need as Dale has shown you.

Hamed NasrRetired IT ProfessionalCommented:
Try: if [Cust Name] is entered as "LastName,FirstName"

LastName = Split ([Cust Name],",")(0)
FirstName = Split ([Cust Name],",")(1)
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

DGWhittakerAuthor Commented:
Thanks Dale & Jim!
I am not getting the results from these expressions, as of yet.
I have attached a document with some pics of the results.
The expressions they provided cannot be used there.  The Split function can be used in your VBA code inside a module while the InStr function can be used in the code, or as the ControlSource of a Textbox in a Form or Report, or as the expression in a calculated field inside a query.  

Hamed NasrRetired IT ProfessionalCommented:
You are looking for splitting a field value entered in a field.
You may use the ideas presented in a query.
Dale FyeOwner, Developing Solutions LLCCommented:
Personally, I despise the idea of "calculated fields".  I guess this is because I'm old school and find that to be no different than performing the calculation in a query.

My assumption, since it was unstated, was that you already have the [Cust Name] field, and that you wanted to:

1.  create two new fields [Name_First], [Name_Last] and run an update query to update those two fields, or
2.  create a query which splits the [Cust Name] into the other two fields on the fly, you could do this any time you wanted to display one or the other.

The Trim(), Mid(), Left(), and Instr() functions will all work within VBA or a query.  I'm surprised that they don't within a "Calculated" field.
Hamed NasrRetired IT ProfessionalCommented:
I gave it another try:
Used expression builder. There is no split function.
Used inStr, Left and Right.
Check this demo database.
table t (fn, l, f)
fn: Short text
l: calculated: Expression: Trim(Right([fn],Len([fn])-InStr(1,[fn],",")))
f: calculated:Expression: Trim(Left([fn],InStr(1,[fn],",")-1))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DGWhittakerAuthor Commented:
This was my first question to the Expert Exchange.
Extremely happy with the amount of help and the fact that I ended up learning so much so fast.
Next step is to figure out how to manage the UpperCase LowerCase, First letter capitalized question.

Problem solved!
Thanks All!
Since calculated columns are created by the database engine (ACE), they can only use functions known to SQL.  That severely limits their usefulness and I would suggest avoiding them because you can accomplish the same thing by using a calculated column in a query.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.