Splitting one field into two separate fields

[ferdib]

Hello,

I have a table with one field 'NAME', consisting of the firstname and surname of a person.

I want to split the content of the 'NAME' field into 2 new fields 'FIRSTNAME' and 'SURNAME'. How do I do this with an update query?

[MMcCarthy]

Hello,

I have a table with one field 'NAME', consisting of the firstname and surname of a person.

I want to split the content of the 'NAME' field into 2 new fields 'FIRSTNAME' and 'SURNAME'. How do I do this with an update query?

Assuming there are no middle names, initials or double barreled names.

You can use the following:

UPDATE TableName SET FIRSTNAME=Left([NAME], InStr([NAME]," ")-1) And SURNAME=Right([NAME], InStr([NAME]," ")+1);
 

Mary

[NeoPa]

Assuming there are no middle names, initials or double barreled names.

You can use the following:

UPDATE TableName SET FIRSTNAME=Left([NAME], InStr([NAME]," ")-1) And SURNAME=Right([NAME], InStr([NAME]," ")+1);

Mary

That would give strange results Mary ;) - Try this :

UPDATE TableName
SET FIRSTNAME=Left([NAME], InStr([NAME]," ")-1),
SURNAME=Mid([NAME], InStr([NAME]," ")+1);

[MMcCarthy]

Sorry ferdib.

Ade why can't I get that stupid 'AND' out of my head. That's not the first time I've done that.

Mary