Parse a string to pull second of three words

[JM420A]

Access 2k3

Query:

I have a string: DOE JOHN MICHAEL (last first middle)
I need to pull JOHN out of it

I can pull out DOE, and get the length of DOE, what I'm having trouble with is how to pull JOHN out. I've been working on it for a while and getting frustrated, so I'm coming to the pros for help.


Here is what I have so far.

SELECT Left(tblUnit_Data_Current.NAME,InStr(1, tblUnit_Data_Current.NAME," ")-1) as LastName,

Len (left(tblUnit_Data_Current.NAME,InStr(1, tblUnit_Data_Current.NAME," ")-1)) as LastNameLength

FROM tblUnit_Data_Current;

Any help, guidance or direction is appreciated.

thx

jm420a

[missinglinq]

For simplicity's sake, I'll call the string NameString:

Mid(NameString, InStr(NameString, " ") + 1, InStrRev(NameString, " ") - InStr(NameString, " ") - 1)

This has been tested with a variety of first, middle and last names. It does, however, require that the string has at least a middle initial, if not a full middle name.

Welcome to Bytes!

Linq ;0)>

[NeoPa]

There are other ways (specifically using Split()) if VBA is used. How it is best done does depend on exactly what can be expected from the data and what is expected in the output.

Are the separator characters always spaces for instance?

Is calling a VBA procedure an acceptable solution?