Parse Suffix from Last Name Field

  • Thread starter Thread starter Bill B.
  • Start date Start date
B

Bill B.

I have a last name field where the suffix like JR, SR, III, II, etc is part
of the data. How can I parse the suffix and leave the last name? TIA
 
I have a last name field where the suffix like JR, SR, III, II, etc is part
of the data. How can I parse the suffix and leave the last name? TIA
Click to expand...

Not easily: what if you have LastName values like "de la Torre" or "von
Beethoven"?

You could use expressions like

NewLast: Left(([lastname] & " ", InStrRev([lastname] & " ", " ") - 1)
Title: Mid([lastname] & " ", InStrRev([lastname] & " ", " ") + 1)

to extract the last "word" in the name, but this (as written) will put "de la
" and "von" in the new last name, and "Torre" or "Beethoven" into the suffix
field.

You could use a criterion such as

LIKE "* JR" OR LIKE "* SR" OR LIKE "* II" and so on using all the possible
suffixes.
 
Assuming they were loaded uniformally, build a table of suffixes with a
totals-make table query.

SELECT Trim(Right(YourTable.[LastName],3)) AS Suffix INTO SuffixList
FROM YourTable
GROUP BY Trim(Right(YourTable.[LastName],3));

Then run an update on new Suffix field in your table using
SuffixList.[Suffix] as criteria on calculated field -
Trim(Right(YourTable.[LastName],3))

If they were not uniform -- Jr, JR., Sr, Sr., 2nd, II, III, 3rd, etc - then
add another field to the SuffixList to put standard suffix and then use it
for update.

Lastly update the LastName field with --
Trim(Left([LastName],
Len([LastName])-Len(Trim(Right(YourTable.[LastName],3)))))
 
Back
Top