Query correctly extracts away middle, but wrongly extracts last name if not middle

[johnmerlino]

Hey all,

There's a problem with this query:

SELECT p.an INTO GeneralWithout
FROM (SELECT Left([name],IIf(InStrRev([name]," ")>1, InStrRev([name]," ")-1,Len([name]))) AS an FROM Forgotten)  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

[ajetrumpet]

John,

I just wanted to say something here. This is like the 10th thread you've started about this same issue. And if I remember right, I've helped you with a lot of it. Have you considered using a utility to do this stuff? I would feel REALLY bad if you have been working on this since 5 or so months ago when we initially talked about this complexity.

[June7]

Yeah, this looks like a lost cause. Doubt could ever get code that could handle every possible name configuration. With the examples you show, only thing I could suggest is that you check the string for multiple spaces. If there is more than one then likely a middle name is present then proceed with extract and keep parsing until only one space is present. This would probably work for: Carson Mary Beth Anne; but what about Van Der Beek James Carl?

[johnmerlino]

but what about Van Der Beek James Carl?

Sure there will be exceptions. But I can't say I know many Van Der Beek James Carl's.