Split column to make two indexs

[ljungers]

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.

[NeoPa]

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.

[ljungers]

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

[MMcCarthy]

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.

UPDATE TableName SET FirstName = Left([FullName], InStr([FullName], " ") - 1), LastName = Right([FullName], InStr([FullName], " ") + 1);
 

Mary

[ADezii]

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:

Left([FullName],InStr([FullName]," ")-1)

'5) In the Update to Row of the [LastName] Field enter the following:

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

[MMcCarthy]

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

Left([FullName],InStr([FullName]," ")-1)

'5) In the Update to Row of the [LastName] Field enter the following:

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

[NeoPa]

A (slightly) better version for the Surname field would be

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.