Access 2003: How to split one (lastname, firstname) field into 2?

[Guest]

I've been at this way too long! Syntax errors, type conversion
errors...help! I've tried ltrim/rtrim left/right...latest follows. fullname
field is formatted "smith, john". from other postings I tried the following
and now a type conversion error coming up...?

UPDATE [members test]
SET FirstName=Left([FullName],InStr([FullName]," ")-1)
WHERE ((([members test].FirstName) Is Null));

 

[Rick B]

You are trying to insert the XX left digits in your first name field? Don't
you have it backwards? Isn't the first name the right portion?

 

[fredg]

I've been at this way too long! Syntax errors, type conversion
errors...help! I've tried ltrim/rtrim left/right...latest follows. fullname
field is formatted "smith, john". from other postings I tried the following
and now a type conversion error coming up...?

UPDATE [members test]
SET FirstName=Left([FullName],InStr([FullName]," ")-1)
WHERE ((([members test].FirstName) Is Null));

i don't quite follow you.
"smith, john" is how the data is stored, yet your code is attempting
to fill the [FirstName] field with the LastName (left portion) of the
full name, i.e. "smith".

If it is the LastName field you wish to fill, then use:

Update [Members Test] Set [Members Test].[LastName] =
Left([FullName],InStr([FullName],",")-1) Where [Members
Test].[LastName] Is Null;


If it is the First Name you wish to fill, then use:

Update [Members Test] Set [Members Test].[FirstName] =
Mid([FullName],InStr([FullName],",")+2) Where [Members
Test].[FirstName] Is Null;

Note: It's not a good idea to use table (or field) names that include
spaces. MembersTest or Members_Test are just as easy to read, yet will
avoid future pitfalls. tblMembersTest would be even better, as it is
immediately clear that the object is a table.