0

I have a field (Called OWNER) that contains the following:

LastName FirstName MiddleInitial Status

everything is separated by spaces and there are no commas after the LastName. I want just the FirstName and MiddleIntial, the status always starts with HRS and may or may not have additional characters. Right now I have:

Right(Trim([tblOutput.OWNER]),Len(Trim([tblOutput.OWNER]))-InStr(1,[tblOutput.OWNER]," "))

But I can't seem to get rid of the additional text containing the 'HRS'. I am sure it is something I am doing wrong when I try to strip the HRS off the above result.

Charles Bunn
  • 70
  • 1
  • 1
  • 9
  • 1
    show examples input/output/desired output – chenchuk Oct 07 '15 at 22:41
  • **Input: HALE VIRGINIA S HRS LICARI JOHN M HRS ETAL PARLIER DONNA HRS HOGGARD NANCY CANFIELD HRS **Present Output: VIRGINIA S HRS JOHN M HRS ETAL DONNA HRS NANCY CANFIELD HRS ***Desired: VIRGINIA S JOHN M DONNA NANCY CANFIELD – Charles Bunn Oct 07 '15 at 23:57

1 Answers1

0

Finally had some time to get back to this. Here is what I have so far, but now the problem is with the Suffix.

Here is the Query:

SELECT tblOutput.PID, Trim(Left([tblOutput.OWNER],InStr(1,[tblOutput.OWNER],"HRS")-1)) AS HRSLoc, Right(Trim([HRSLoc]),Len(Trim([HRSLoc]))-InStr(1,[HRSLoc]," ")) AS FName, Left([HRSLoc],InStr(1,[HRSLoc]," ")-1) AS LName, Right([FName],InStr(StrReverse([FName])," ")) AS Suffix FROM tblOutput INNER JOIN green ON tblOutput.PID = green.PID ORDER BY tblOutput.PID;

Here is a screen shot of the output:

Acess query output

I am unable to make the query grab matching items from a list for the Suffix such as "JR", "SR", "MD", Etc. So right now I have the last name correct but the First Name also contains the Suffix, which I need to remove so I can rearrange the name in output as FirstName + MiddleInitial (if there is one) + LastName + Suffix.

Charles Bunn
  • 70
  • 1
  • 1
  • 9