Update Query... How to?

[Noozer]

My MS Access has a column called NAME. I want to break the data in the field
into two parts so I've added the columns FNAME and LNAME. Now I need an
update query that will take the value in name and place the first name into
the FNAME field and the last name into the LNAME field.

I'm assuming that the last name begins after the last space in the name.

I tried to create a query in Access with little success... The query I've
tried to write is as follows (but does not work) is...

UPDATE Users SET
FName=Fn IN (SELECT Fn FROM (SELECT LEFT(Name, INSTR(Name," ")-1)) AS Fn
FROM Users ),
LName=Ln IN (SELECT Ln FROM (SELECT MID(Name,INSTRREV(Name," ")+1)) AS Ln
FROM Users);

 

[Baz]

My MS Access has a column called NAME. I want to break the data in the field
into two parts so I've added the columns FNAME and LNAME. Now I need an
update query that will take the value in name and place the first name into
the FNAME field and the last name into the LNAME field.

I'm assuming that the last name begins after the last space in the name.

I tried to create a query in Access with little success... The query I've
tried to write is as follows (but does not work) is...

UPDATE Users SET
FName=Fn IN (SELECT Fn FROM (SELECT LEFT(Name, INSTR(Name," ")-1)) AS Fn
FROM Users ),
LName=Ln IN (SELECT Ln FROM (SELECT MID(Name,INSTRREV(Name," ")+1)) AS Ln
FROM Users);

UPDATE Users SET FName = Trim(Left([Name],InStrRev([Name]," "))),
LName = Trim(Mid([Name],InStrRev([Name]," ")))

This assumes that all your fields are of the form FName + " " + LName. If
you get errors with the string manipulation functions, you will need to add
a WHERE clause to exclude records where the Name field is not in a suitable
form for splitting like this.