Separating data in fields

[Judy]

Hello...once again I find myself turning to my favorite web site for access help. This is probably simple, but taking me way too much time to figure out. I have a table that has a name field that includes the entire name (lname, fname, mi). The table I want to import this data into has a separate field for lname, fname and mi. What function can I use in a query to separate the field into three at the ',' between names? Any suggestions?

Thanks!

 

[fmm]

LastName = TRIM(LEFT([FullName],SEARCH(",",[FullName],1)-1))

FirstName = TRIM(LEFT(TRIM(RIGHT([FullName],LEN([FullName]) - SEARCH(",",[FullName],1))), SEARCH (",",TRIM(RIGHT([FullName],LEN([FullName]) - SEARCH (",",[FullName],1))),1)-1))

MiddleInitial =TRIM(RIGHT(TRIM(RIGHT([FULLNAME],LEN([FULLNAME]) - SEARCH(",",[FULLNAME],1))),LEN(TRIM(RIGHT([FULLNAME],LEN([FULLNAME]) - SEARCH(",",[FULLNAME],1)))) - SEARCH(",",TRIM(RIGHT([FULLNAME],LEN([FULLNAME]) - SEARCH(",",[FULLNAME],1))),1)))


I think you have to have the commas for this to work.


[This message has been edited by fmm (edited 10-15-2001).]

 

[Lynn63]

Check this out:
http://www.mvps.org/access/strings/str0001.htm