Parse Suffix from Last Name Field

[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

 

[John W. Vinson]

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

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.

 

[KenSheridan via AccessMonster.com]

The following function would remove the suffix, assuming its an unbroken
substring, and would leave a name without a suffix unaffected, and would also
handle Nulls:

Function RemoveSuffix(varName As Variant) As Variant

Dim intSpacePos As Integer

If Not IsNull(varName) Then
intSpacePos = InStr(varName, " ")
If intSpacePos > 0 Then
RemoveSuffix = Left(varName, intSpacePos - 1)
Else
RemoveSuffix = varName
End If
End If

End Function

But, as John points out, would give an erroneous result with names like 'de
los Angeles'.

The real solution of course is to hive the suffixes off in to separate column
in the table.

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

Correction: I meant to say:

Function RemoveSuffix(varName As Variant) As Variant

Dim intSpacePos As Integer

If Not IsNull(varName) Then
intSpacePos = InStrRev(varName, " ")
If intSpacePos > 0 Then
RemoveSuffix = Left(varName, intSpacePos - 1)
Else
RemoveSuffix = varName
End If
End If

End Function

Ken Sheridan
Stafford, England

 

[KARL DEWEY]

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)))))