if field has two spaces pull content to the left of second space

[johnmerlino]

Hey all,



I have this query:

SELECT p.aname INTO GenericForgottenWithoutInitial
FROM (SELECT Left([TheName],IIf(InStrRev([TheName]," ")>1, InStrRev([TheName]," ")-1,Len([TheName]))) AS aname FROM TheForgotten) AS p;

Problem is it pulls content to left of first space because of this part:

InStrRev([TheName]," ")-1,Len([TheName]))


I would like it to pull content to left of the second, not first space.

Thanks for response