DGWhittaker
asked on
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:
#Type!
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.
#Type!
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try: if [Cust Name] is entered as "LastName,FirstName"
LastName = Split ([Cust Name],",")(0)
FirstName = Split ([Cust Name],",")(1)
LastName = Split ([Cust Name],",")(0)
FirstName = Split ([Cust Name],",")(1)
ASKER
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.
Formula-Results-150912.docx
I am not getting the results from these expressions, as of yet.
I have attached a document with some pics of the results.
Formula-Results-150912.docx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are looking for splitting a field value entered in a field.
You may use the ideas presented in a query.
You may use the ideas presented in a query.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Dennis
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!
Dennis
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.
Jim.