Access - split name in format last name, first name

Please offer how to separate this name format.

The formula I have keeps the "," after the last name.

Thank you
exp vgAsked:
Who is Participating?

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

x
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.

aikimarkCommented:
Your question lacks context (code, query, form, report, etc)
You should post representative data examples
You should post the code you've written that isn't working for you
exp vgAuthor Commented:
The request is for a code - so it is not possible for me to provide one.

Thank you.
aikimarkCommented:
The formula I have
Then post the formula

You should post representative data examples
Do not ask experts to operate in the dark.  It is an approach that does not result in solutions.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
as aikimark had mentioned, try to provide as much info as possible, so that a straight and clear answer may be provided in a shorter possible time. Ambiguous explanation in the question may also discourage experts in answering the question.

as per your question, I made the assumption it's in form, or in a pure script environment, so you probably can try like this:

Fullname = FirstName + LastName ( with delimiter = , )

Private Sub cmdTest_Click()
    Dim n As String
    n = txtFullName.Value 'like n = "someName, anotherName"
    idx = InStr(1, n, ",", vbTextCompare)
    If idx > 0 Then
        txtFirstName.Value = Trim(Left(n, idx - 1))
        txtLastName.Value = Trim(Mid(n, idx + 1))
    Else
        txtFirstName.Value = n
        txtLastName.Value = ""
    End If
End Sub

Open in new window


pls customize accordingly
Database1.accdb
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I agree with akimark - it's nearly impossible to help when we don't really know what we're working with.

Not to mention that is reads like "Do my work for me, code monkey".
exp vgAuthor Commented:
I do not mean for anyone else to do my work for - I have tried the options I tried - and they did not work.

I was just at a loss.

Please do not take it that I did not try and I expect you to do the work for me.

Thank you everyone.
exp vgAuthor Commented:
Thank you everyone. Again, there was a  misunderstanding in my question - I thought it was best for all of you to start fresh since nothing I tried worked.
Jeffrey CoachmanMIS LiasonCommented:
If your data is in this form:
    Bill, Sanders

Then you can use a query like this:

SELECT YourTable.FullName, Left([FullName],InStr([FullName],",")-1) AS FirstName, Right([FullName],Len([FullName])-InStr([FullName],",")-1) AS LastName
FROM YourTable;
Dale FyeOwner, Developing Solutions LLCCommented:
Or you can use the function I provided you the other day, and instead of splitting on the space (" "), use the comma:

Split([FullName], ", ")(0)
exp vgAuthor Commented:
Thank you everyone.
exp vgAuthor Commented:
I've requested that this question be deleted for the following reason:

As suggested by the moderator. Thank you everyone for your help.
aikimarkCommented:
@exp vg

As suggested by the moderator
You have misunderstood the moderator's comment.  What EE expects you to do, part of the Terms Of Use you agreed to, is to fully participate in your question threads.

* The participating experts have asked you to post some examples of names you need to split.  You have not done this.
* The participating experts have asked you to post the context (query, form, report, etc.) for this problem.  You have not done this.
* The participating experts have asked you to post the "formula" you are currently using that does not work.  You have not done this.

I am stopping this delete request, allowing you to participate with the experts in the solution to your problem.
exp vgAuthor Commented:
Let is try this again,

I have a column, where it is a full name, In the format Last Name, First Name.

I am able to replace the "," with "" and then separate the names.

Please offer a solution to separate the name in just one step rather than the two.

Hope this makes sense.

Thank you.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Where are you doing this? In a Query, or in VBA?

If it's in a Query, then you can use two calculated columns to do this:

FName = Left([First Name], InStr([First Name], ",")-1)
LName = Right([First Name], Len([First Name]) - InstrRev([First Name], ","))

This would result in columns named "FName" and "LName" in your query.

In VBA, you can do much the same thing by declaring the FName and LName as String variables, and then using that as needed.

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
exp vgAuthor Commented:
This works great. I did a review of my data, and just realized something - not all the name fields are separated by a comma. Some are separated by a period or a space.

I tried modification with this
LName: Right([Employee Name],Len([Employee Name])-InStrRev([Employee Name],",","."," "))

to add the space and dot as separators as well - without luck.

Please advise.
exp vgAuthor Commented:
Basically, this name field can be separated by a comma, space, or period.

Thank you.
exp vgAuthor Commented:
Thank you.
Dale FyeOwner, Developing Solutions LLCCommented:
Given your latest response, I'd go with something like:

FName = Left([Employee Name], InStr(Replace(Replace([Employee Name], ",", " "), ".", " "), " ")-1)

The Replace function will replace all occurrences of a character with another character.  In this case, I've used the Replace function to replace commas and periods in your [Employee Name] field before using the Instr() function to find the first occurrence of a space in that field.  This segment replaces the commas:

Replace([Employee Name], ",", " ")

And then wrapping it within another Replace statement would replace any periods that may or may not remain in that string:

Replace(Replace([Employee Name], ",", " "), ".", " ")

This will not actually change the value of the [Employee Name] field, but will allow you to simply search for a space.

LName = Mid([Employee Name], InStrRev(Replace(Replace([Employee Name], ",", " "), ".", " "), " ") -1)

Note that this uses the InstrRev( ) function which finds the first space in a string, searching from right to left.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Dale: He's asked that same question separately:

http://www.experts-exchange.com/questions/28704101/Access-Separate-delimited-field-with-comma-or-dot.html

exp vg: Please don't continue adding to your question after you've received a solution, and ESPECIALLY when you've already posted another question for that new requirement. You can post a link to the new question here - although chances are the Experts have already seen it - but don't continue to ask for additional help here when you've already done so elsewhere. That'll get you in trouble with the Mods, and many Experts will simply stop trying to help you if they feel like you're wasting their time.
Dale FyeOwner, Developing Solutions LLCCommented:
I know, Scott.  The OP is over-anxious.  I hate to see him/her posting these almost identical questions over and over again, before he/she even gets an answer to the previous question.  Way too much overlap in these questions.
exp vgAuthor Commented:
Thank you for the suggestion. I thought it may actually help experts more if I highlight key areas of where I have troubles after attempting suggestions.

I will definitely keep this in mind.

Nothing detrimental intended - I just wanted to focus on the areas where I was having challenges - and thought this would be easier for you.
Dale FyeOwner, Developing Solutions LLCCommented:
@exp vg,

No problem.  The challenge is that you have asked so many questions that are almost identical, it is hard for us experts to keep up with.  Generally, it is best if you provide context and sample data when asking questions here on the forum.  The context is important because we will many times recommend alternatives to the path that you are traveling, after many years of experience.

It is also helpful if you provide what you have tried, rather than simply "doesn't work" or "cannot get this to work".  If we know what you have tried we can:
1.  Identify syntax errors
2.  Identify other solutions.

It is always good to see someone who is eager to learn!  Keep it up.

BTW, although you will get answers over the weekend, experts have lives too, so if you are going to post questions on the weekends, plan on longer delays between when you post and when you get a response.

Dale
exp vgAuthor Commented:
I emphasize my gratitude to all of you - and all my questions, and how I post - are only intended to make it easier for you as well.
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.