Break apart names in query

[Leesha]

Hi,

I have a query that contains the first and last name in the same cell. This database was built ages ago and didn't separate out the first and last names into separate fields. I need to run a query that would take the [EmployeeName] and break it into [FirstName] and [LastName].

Thanks,
Leesha

[HansV]

If EmployeeName is always of the form "John Doe", you can use calculated columns:

FirstName: Left([EmployeeName],InStr([EmployeeName]," ")-1)

LastName: Mid([EmployeeName],InStr([EmployeeName]," ")+1)

But if there are names such as "Mary J. Evans" the middle initial will be seen as part of the last name.

[Wendell]

If you do have the situation Hans indicates, I often simply take the data out to Excel, and then use the separate data into columns feature to progressively push the names apart, and then bring it back into Access. The advantage of that approach is that you can visually see how the separation is working. It also lets you quickly fix the issue with last names that include spaces and other weird cases.

[Leesha]

Thanks to both of you!!! Fortunately there are no middle initials in this short list of employees, but I'm sure as move on in this database to the list of students, I will find that issue!!

Leesha