Prev: Linking Excel Into Access Issue
Next: Cumulative Unique Record Count
From: Bill B. on 7 May 2010 15:58
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
From: John W. Vinson on 7 May 2010 16:21
On Fri, 7 May 2010 12:58:01 -0700, Bill B. <BillB(a)discussions.microsoft.com>
wrote:

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

John W. Vinson [MVP]
From: KenSheridan via AccessMonster.com on 7 May 2010 16:40
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

Bill B. wrote:
>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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1

From: KenSheridan via AccessMonster.com on 7 May 2010 16:43
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1

From: KARL DEWEY on 7 May 2010 17:02
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)))))

--
Build a little, test a little.


"Bill B." wrote:

> 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
 | 
Pages: 1
Prev: Linking Excel Into Access Issue
Next: Cumulative Unique Record Count