Separating the name fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have imported a ASCII file to Access but when I did the Name fields came in
as one field formatted " Lastname, Firstname Middlename". Any suggestions as
to how I can separate the it into separate fields?
 
Well, separating the Last Name out should be easy, since there's the comma in the Name field:

SELECT Left([Name],InStr([Name],",")-1) AS LastName,
Right([Name],Len([Name])-InStr([Name],",")-1) AS OtherNames

If you save that as a Query, then you should be able to do the same again using the space to separate out the First Name from the Middle Name:

SELECT Left([OtherNames],InStr([OtherNames]," ")-1) AS FirstName,
Right([OtherNames],Len([OtherNames])-InStr([OtherNames]," ")-1) AS MiddleName

Note that this assumes that there's always a space following the comma after the last name, and that the first name and middle name are separated by one space. If you can't guarantee that the contents of the Name field always conforms to those rules, you SELECT statement will need to become more sophisticated.

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/Uwe/Forum.aspx/access-externaldata/7075
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=67b822f48558452c8236fb0a085af357
*****************************************
 
Back
Top