HOW TO SEPARATE NAME FIELD

[pauld]

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.

 

[fredg]

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.

Besides having the field contain more than one data item (the First
and Last names), the field named "Name" can itself create a problem.

Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
For some additional reserved words, see the Microsoft KnowledgeBase
article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

To answer your question about separating the names, as long as all of
the names are in exactly LastName comma space FirstName order, it's
quite easy.

Add 2 new fields to your database.
FirstName and LastName

Then create an Update query. Here is the query SQL.

Update YourTable Set YourTable.[LastName] =
Left([FullName],InStr([FullName],",")-1), YourTable.[FirstName]=
Mid([FullName],InStr([FullName],",")+2) Where YourTable.[FullName] is
not null

Change the table and field names as needed.
If you do retain that field named "Name" make sure you re-name it to
something else. FullName sounds good to me.

 

[Anand]

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

 

[pauld]

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.

 

[pauld]

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.

 

[John Spencer MVP]

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

 

[pauld]

Thank you...using your Mid expression worked perfectly.

I also noticed that the original expression for LastName is causing the
comma to be copied. Any way to revise the LastName expression to make sure
the comma doesn'rt appear.

Thanks

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.

 

[John Spencer MVP]

I would use an expression like the following.

Left([SkipperName],Instr(1, [SkipperName] & ",", ",")-1)

Again, applying the trim function might take care of leading or trailing spaces.

Trim(Left([SkipperName],Instr(1, [SkipperName] & ",", ",")-1))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you...using your Mid expression worked perfectly.

I also noticed that the original expression for LastName is causing the
comma to be copied. Any way to revise the LastName expression to make sure
the comma doesn'rt appear.

Thanks

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.

 

[pauld]

Again I thank you.

The first expression below worked!

I would use an expression like the following.

Left([SkipperName],Instr(1, [SkipperName] & ",", ",")-1)

Again, applying the trim function might take care of leading or trailing spaces.

Trim(Left([SkipperName],Instr(1, [SkipperName] & ",", ",")-1))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you...using your Mid expression worked perfectly.

I also noticed that the original expression for LastName is causing the
comma to be copied. Any way to revise the LastName expression to make sure
the comma doesn'rt appear.

Thanks

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

pauld wrote:
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.