issue with extracting portion of name with access query

[johnmerlino]

Hey all,

There's a problem with 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;

Basically I am trying to extract away a middle initial (or middle name) if it exists. It correctly extracts away middle initial in this scenerio:

Dougal Anthony John

outputing this:

Dougal Anthony

However, if the name has no middle initial:

Alvarado Michael

then it outputs this incorrectly:

Alvarado

whereas I wanted it to remain as is since no middle initial existed:

Alvarado Michael



Thanks for response

 

[stopher]

This seems to work:

 Left([TheName],InStr([TheName]," ")) & Right([TheName],Len([TheName])-InStrRev([TheName]," "))

hth
Chris

 

[johnmerlino]

Thanks for response.