Split a field in two new fields

[Guest]

I have a field [name] that include "firstname,lastname". All names have a ,
(comma) separating de lastname and first name. The length before and after
the comma are not constant. I need a querry to split the name in two
fields. From first position before the comma insert in a new field named
LastName and everything after the comma in new field named FirstName. Any
Help? Please?

 

[Rick B]

Have you done a search? This is a very common question.

Go read the previous posts that address this topic.

 

[Brendan Reynolds]

UPDATE tblTest SET tblTest.firstname =
Left$([fullname],InStr(1,[fullname],",")-1), tblTest.lastname =
Mid$([fullname],InStr(1,[fullname],",")+1);

You're sure there's no space after that comma? You'll need to modify the
above slightly if there is - change the "+1" to "+2".

 

[Vincent Johns]

UPDATE tblTest SET tblTest.firstname =
Left$([fullname],InStr(1,[fullname],",")-1), tblTest.lastname =
Mid$([fullname],InStr(1,[fullname],",")+1);

You're sure there's no space after that comma? You'll need to modify the
above slightly if there is - change the "+1" to "+2".

Or, to avoid spaces, change that last part to ...

tblTest.lastname =
Trim$(Mid$([fullname],InStr(1,[fullname],",")+1));


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.