Qry to get first name from 'Jones, Tom B.'? 1

[newyorkny]

Good afternoon:

I am trying to write a query that will slurp out the middle name from a field "Name" in table "tbl_Name."

The field is presently in the format:

Last, First MI (if any) or

Smith, Bob C

There is always that comma then space then firstname then space.

I am grateful for any guidance. Came up with a good VBA solution thanks to some assitance but I am very hopeful of a query that can handle this.

THANKS!!

 

[Tranman]

NY,
This should do the job--at least it works with my limited test set, which included fields like:
Smith, John J.
Smith, John
Smith, John (with a space after the 'n')

(always returns 'John')

SELECT IIf(InStr([Name]," ")=InStrRev([Name]," "),Mid([Name],InStr([Name]," ")+1,Len([Name])-InStr([Name]," ")),Mid([Name],InStr([Name]," ")+1,InStrRev([Name]," ")-(InStr([Name]," ")+1))) AS FName 
FROM tbl_Name;

Hope that helps. I think I would personally code a function to do this, but maybe there's some reason not to...

Tranman

 

[newyorkny]

Thanks, Tran:

I think that should be perfect...I see what I was doing wrong with my query!

Thanks, again!