Try using the Mid function instead
Trim(MID([SkipperName],Instr(1,[SkipperName] & ",",",")+1))
The Trim will remove any leading or trailing spaces.
Appending the comma to SkipperName in the call to Instr will ensure that if
there is no comma you will return a blank as the result. Otherwise you will
return the entire name for both lastname and first name
Anand's expression should have read
Update TableName
Set FirstName = Right([Name],Len([Name])-Instr([Name],","))
And even that will return unexpected results if there is no comma in SkipperName
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
The first expression you provided worked. All of the "last names" are now in
the field called LastName
The second expression (which I was asking about) doesn't work correctly. I
seem to get a few extra letters (from the last name). Also, the comma is
there. Is there any way to get rid of the comma?
The actual full field name (with last, first) I was referring to is
"SkipperName" and I'm trying to break it apart into FirstName and LastName.
Could you please resend the expression for the "right" part of the full
field? This is where I want to take the first name from the SkipperName field
and put it into the FirstName field
Thanks.
:
Thank you.
Could you please explain what the "len" means in the second expression
And why is there a "minus sign" after Name in that second expresion?
I really appreciate the help.
Thanks.
:
You can do this by update Query.
As you have field Name in Database. Just create FirstName and LastName in
database.
then run query..
if your name separated by "," or space.
if NAME="Anand Kumar" in database
Update TableName Set LastName= Left(Name, instr(Name," ")-1) - ---->
values goes to DB=Anand
Update TableName Set FirstName= right(Name,len(Name- instr("Name","
")------->Values goes to DB=Kumar
This way u can update the lastname and firstname.
Tell me if it will not work.''
I have an Access 2003 db with a field called Name.
It contains the name as Last, First (I know,...bad !!)
I want to split the data into 2 separate fields called LastName and
FirstName.
I have a rough idea on how to do this but would appreciate help with the
specific expresion I'd need to use.
Would I do this as an Update Query? Or a Macro using SetValue?
Thanks in advance for your help.