Access - split name in format last name, first name

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

The request is for a code - so it is not possible for me to provide one.

Thank you.

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.

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

Select allOpen in new window

pls customize accordingly
Database1.accdb

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

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.

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.

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;

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)

Thank you everyone.

I've requested that this question be deleted for the following reason:

As suggested by the moderator. Thank you everyone for your help.

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

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.

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.

Basically, this name field can be separated by a comma, space, or period.

Thank you.

Thank you.

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.

Dale: He's asked that same question separately:

https://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.

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.

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.

@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

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.