splitting a name field

[Roni Sutton]

I have an imported table with a name field in the last, first format. I need to go through each record and split the first and last names into two different fields. Each name contains a comma, but of course, each last name is a different length. I have created blank fields to store the last and first names, I'm just not sure how to get the data to them using code.

Thanks for any help!!

Roni

 

[Matt Gilbert]

Try using the InStr function to find the comma and then take the data to the left and right of it.

 

[Daryl]

Checkout this Microsoft document:
http://support.microsoft.com/support/kb/articles/Q115/9/15.asp?LN=EN-US&SD=gn&FR=0&qry=parse&rnk=3&src=DHCS_MSPSS_gn_SRCH&SPR=ACC97

 

[raskew]

SELECT tblClients.Name, Left([name],InStr([name],",")-1) AS LName, Trim(Mid([name],InStr([name],",")+1)) AS FName FROM tblClients;

 

[Roni Sutton]

Thanks guys! It is wonderful to know that this stuff doesn't just go away. I used the first reply that I got back when I first posted this and it worked wonderfully. However, I can ALWAYS use microsoft articles and SQL statements. This forum is such a wealth of information. I'm so glad I found it.

Thanks again!

Roni