By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,374 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Split column to make two indexs

100+
P: 114
Can a Access field let's say called FullName that contains a full name (first last). I I would like to create 2 new columns called FirstName and LastName using the the data in the FullName filed. Can this be done?

Thanks.
Dec 14 '06 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,308
Yes. It can be done.
What do you need exactly?
There are many ways depending on exactly what you're after - display or update for instance.
Dec 15 '06 #2

100+
P: 114
I'm trying to update the table. Table was loaded via import. The table has a field named FullName. Now I wish to add two more columns to the table. One called FirstName and the other called LastName.
I need to use the field with full to extract the first name and the last name.
exam: FullName="John Doe" then I want to use FullName to create FirstName=John LastName=Doe
Dec 15 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm trying to update the table. Table was loaded via import. The table has a field named FullName. Now I wish to add two more columns to the table. One called FirstName and the other called LastName.
I need to use the field with full to extract the first name and the last name.
exam: FullName="John Doe" then I want to use FullName to create FirstName=John LastName=Doe
Add two fields to the table FirstName and LastName. Now run the following update query.

Expand|Select|Wrap|Line Numbers
  1. UPDATE TableName SET FirstName = Left([FullName], InStr([FullName], " ") - 1), LastName = Right([FullName], InStr([FullName], " ") + 1);
  2.  
Mary
Dec 15 '06 #4

ADezii
Expert 5K+
P: 8,616
Can a Access field let's say called FullName that contains a full name (first last). I I would like to create 2 new columns called FirstName and LastName using the the data in the FullName filed. Can this be done?

Thanks.
'1) Add a [FirstName] Column to the underlying Table
'2) Add a [LastName] Column to the underlying Table
'3) Create an Update Query consisting odf the [FullName], [FirstName], and [LastName] Fields. In the [FullName] Field, set the criteria to Is Not Null
'4) In the Update to Row of [FirstName] enter the following:
Expand|Select|Wrap|Line Numbers
  1. Left([FullName],InStr([FullName]," ")-1)
'5) In the Update to Row of the [LastName] Field enter the following:
Expand|Select|Wrap|Line Numbers
  1. Right([FullName],Len([FullName])-InStr([FullName]," "))
NOTE: This will work fine as long as there are spaces between the First and Last names in the [FullName] Field and there are no middle initials in the [FullName] Field. If either one of these conditions exist, the situation becomes a little more complex. Hope this helps...
Dec 15 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
'1) Add a [FirstName] Column to the underlying Table
'2) Add a [LastName] Column to the underlying Table
'3) Create an Update Query consisting odf the [FullName], [FirstName], and [LastName] Fields. In the [FullName] Field, set the criteria to Is Not Null
'4) In the Update to Row of [FirstName] enter the following:
Expand|Select|Wrap|Line Numbers
  1. Left([FullName],InStr([FullName]," ")-1)
'5) In the Update to Row of the [LastName] Field enter the following:
Expand|Select|Wrap|Line Numbers
  1. Right([FullName],Len([FullName])-InStr([FullName]," "))
NOTE: This will work fine as long as there are spaces between the First and Last names in the [FullName] Field and there are no middle initials in the [FullName] Field. If either one of these conditions exist, the situation becomes a little more complex. Hope this helps...
ADezii is of course right use his code for the LastName instead of mine.

Mary
Dec 15 '06 #6

NeoPa
Expert Mod 15k+
P: 31,308
A (slightly) better version for the Surname field would be
Expand|Select|Wrap|Line Numbers
  1. Mid([FullName],InStr([FullName]," ")+1)
The reason I asked the question (thanks for the reply btw) was mainly to determine whether display or update, but also for you to have a careful think about what rules the data MUST adhere to.
If you can say, hand on heart, that there will never be an exception to the format {Firstname}SPACE{Surname} then this will work for you.
Otherwise, you may be better advised to create a global function which handles all cases for you. It would need to handle returning both names individually.
Dec 15 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.