HELP? Select statement to get first & last name from full name Field

[ckwizard77]

I need help creating a query that will extract the first, last and middle Intial from a field (ex. FullName).
The fields may include data like this:
Coward, Sam
Truman, Harry S
Case, Jr., Justin W
I need to get Justin w Case, Jr. or Justin W Case Jr.
I have tried for hours using InStr, Left, Right and Mid but I can not come up with the correct data.
Please Help!

 

[Remou]

Hi
Here is a nice, long debate on just that topic: Separating Name Query thread701-794191

Have fun!

 

[akaivyleaf]

I don't think you can write a blanket select statement that will get what you want. The best you can do, if you included a comma in the fullname field, is to separate everything into at least two separate colums Last Name which would be everything before the comma, and then First name and Initial which would be everything after the comma, then you can go back and concatanate from both of these fields to get at least first and last initial.

The options you're trying now, Left, Mid and Right require Access to "count" which doesn't work because of the variable letters in each person's Name.