Split Field

[cbs604]

I have inherited a database that contains a couple of fields labelled LastName and GivenNames. The data in the GivenNames fields contains one or two names.

It is now time to split this field into FirstName and MidName. How can I do this?

I know it is simple in Excel, and I thought of exporting to excel, splitting the field and importing it back. But there are over 50,000 records and the table has about 30 fields.

Is there a easier method available in Acess?



Cheers,
Brodie

 

[Rofeu]

you can create a query that returns everything, except GivenNames, and insert two fields:

FirstName: Trim(Left([GivenNames],InStr([GivenNames]," ")))
MidName: Trim(Right([GivenNames],Len([name])-InStr([GivenNames]," ")))

Cheers,

Roel

PS for the future: I believe there are a seven odd forums specifically for Access, you'll probably get quicker/better responses there.

 

[SkipVought]

Hi,

Depends on the data. Have you done an analysis of the data in this column to know, to a certainty, what the logic is to separate the names?

Skip,
[sub]
[red][/red]
[/sub]

 

[cbs604]

That worked a treat, Roel.

I only used the first example to test it, however, I cannot figure out why it returns a null when there is only one name in the GivenNames field.

This field BTW, contrains up to 4 names, but I am only interested in the 1st and 2nd.



Cheers,
Brodie

 

[SkipVought]

Also take a look at Data > Text to columns... using the SPACE Delimiter.

Skip,
[sub]
[red][/red]
[/sub]