If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Parse Suffix from Last Name Field
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 |
#2
|
|||
|
|||
Parse Suffix from Last Name Field
On Fri, 7 May 2010 12:58:01 -0700, 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 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] |
#3
|
|||
|
|||
Parse Suffix from Last Name Field
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/For...eries/201005/1 |
#4
|
|||
|
|||
Parse Suffix from Last Name Field
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/For...eries/201005/1 |
#5
|
|||
|
|||
Parse Suffix from Last Name Field
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 |
Thread Tools | |
Display Modes | |
|
|