hi,

i just need help in figuring this one out I have a table with a Name field in which the last and first name are seperated by a comma(,) , now i want this data to go to other table as First name and last name in two different fields of other table how can i do this in microsofyt access.

thanks
srinivas somanchi

For last name use

Left([WholeNameField],instr(1,[WholeNameField],",",1)-1)

For first name use

right([WholeNameField],Len([WholeNameField])-instr(1,[WholeNameField],",",1)-1)

This will work as long as you only have a first and last name in this field. If you have a middle name or initial then it will be a little more complex but doable.

HTH
Ken

thanks for the help let me try and see if it works out

You're welcome, let me know if this works for you.

Ken

hi ,
i did not understand where to type in the code which you gave to get the result would you tell me elaborately,

thanks

You can use that in the field location in query design view

like this in sql design view if you just want to copy/paste

SELECT Left([WholeNameField],instr(1,[WholeNameField],",",1)-1) AS LastName, right([WholeNameField],Len([WholeNameField])-instr(1,[WholeNameField],",",1)-1) AS FirstName FROM YourTableName

Just replace "YourTableName" with the proper table

HTH
Ken

i typed in the following:

SELECT Left([Name],instr(1,[Name],",",1)-1) AS LastName, right([Name],Len([Name)-instr(1,[Name],",",1)-1) AS FirstName
FROM vasutemp;

it gave this error:Wrong number of arguments used

Right([Name],Len([Name]-instr(1,[Name],",",1)-1) AS FirstName
FROM vasutemp;

I GUESS THERE IS SOMETHING WRONG WITH THE ABOVE LINE BECAUSE WHEN I REMOVE THAT PART FROM THE QUERY I GET THE LASTNAMES SEPERATED WITHOUT ANY PROBLEM

HI KEN!

THANKS FOR YOUR HELP EVERYTHING IS WORKING FINE NOW THANKS FOR YOUR HELP......

You're welcome, glad to help.