Move from Name Field to Suffix feild 2

[TJVFree]

I'm trying to find a way that if one of my customers has a Suffix in his name that it would remove it from the Name field and add it to the Suffix field. I would like to run this through a ms access db

Any Idea's

See attachment for layout


Before
NAME LAST Suffix
John Sr Smith
JohnSir Smith
John III Smith
John Jr Smith
Sr John Smith
Sr John Smith
III John Smith
Jr John Smith



After
NAME LAST Suffix
John Smith Sr
John Smith Sir
John Smith III
John Smith Jr
John Smith Sr
John Smith Sir
John Smith III
John Smith Jr


TCB

 

[dhookom]

I would expect the best solution would be to work with a copy of the table for testing. Create a table of all known suffixes and use it to extract from one field and add it to another. Does your original table have a primary key field or fields?

BTW: you should use the Pre TGML tag for your data table so we can see how your data is actually stored.

Before
[pre]
NAME LAST Suffix
John Sr Smith
JohnSir Smith
John III Smith
John Jr Smith
Sr John Smith
Sr John Smith
III John Smith
Jr John Smith
[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Public Function GetSuffix(varName As Variant) As Variant
  Dim strNames() As String
  Dim strSuffixes() As String
  Dim i As Integer
  Dim j As Integer
  strSuffixes = Split("Sr,Jr,Sr.,Jr.,I,II,III,IV,Sir", ",")
  If Not IsNull(varName) Then
    strNames = Split(varName, " ")
    If UBound(strNames) > 0 Then
    For i = 0 To UBound(strNames)
      For j = 0 To UBound(strSuffixes)
         If Trim(strNames(i)) = strSuffixes(j) Then
          GetSuffix = strSuffixes(j)
          Exit Function
        End If
      Next j
    Next i
    End If
  End If
End Function

in a query

SELECT tblNames.First, tblNames.Last, getSuffix([first]) AS FoundSuffix
FROM tblNames;

give you something like

First	        Last	FoundSuffix
John Sr 	Smith	Sr
John III	Smith	III
Sr John	        Smith	Sr
John Sir	Smith	Sir
III John	Smith	III
Jr John	        Smith	Jr

I would save into a new table and verify the results
then to clean up

Public Function RemoveSuffix(varName As Variant, varSuffix As Variant) As Variant
  If Not IsNull(varName) And Not IsNull(varSuffix) Then
     RemoveSuffix = Trim(Replace(varName, varSuffix, ""))
  End If
End Function

in a query

SELECT tblNames2.First, tblNames2.Suffix, RemoveSuffix([First],[Suffix]) AS CleanFirst
FROM tblNames2;

results

First	  Suffix CleanFirst
John Sr	  Sr	 John
John III  III	 John
Sr John	  Sr	 John
John Sir  Sir	 John
III John  III	 John
Jr John	  Jr	 John

 

[dhookom]

I like MajP's code since it should be very fast and his use of Split() with a space is nice. I would urge caution when performing any mass updates. If you truly have a value like "JohnSir" then I don't expect any simple function is going to work correctly.

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Hi again MajP, for some reason its not pulling the suffixes from the FirstName feild. I attached the database if that help

Public Function GetSuffix(varName As Variant) As Variant
Dim strNames() As String
Dim strSuffixes() As String
Dim i As Integer
Dim j As Integer
strSuffixes = Split("Sr,Jr,Sr.,Jr.,I,II,III,IV,Sir", ",")
If Not IsNull(varName) Then
strNames = Split(varName, " ")
If UBound(strNames) > 0 Then
For i = 0 To UBound(strNames)
For j = 0 To UBound(strSuffixes)
If Trim(strNames(i)) = strSuffixes(j) Then
GetSuffix = strSuffixes(j)
Exit Function
End If
Next j
Next i
End If
End If
End Function
in a query
CODE



SELECT tblNames.First, tblNames.Last, getSuffix([first]) AS FoundSuffix
FROM tblNames;


Your File has been successfully uploaded to ENGINEERING.com.

Your file's link is:

http://files.engineering.com/getfil...1b6-5407d617235c&file=Testing_Database1.accdb

TCB

 

[dhookom]

Apparently there is an issue matching upper and lower case. Try change the function to:

Public Function GetSuffix(varName As Variant) As Variant
  Dim strNames() As String
  Dim strSuffixes() As String
  Dim i As Integer
  Dim j As Integer
  strSuffixes = Split("SR,JR,SR.,JR.,I,II,III,IV,SIR", ",")
  If Not IsNull(varName) Then
    varName = UCase(varName)
    strNames = Split(varName, " ")
    If UBound(strNames) > 0 Then
    For i = 0 To UBound(strNames)
      For j = 0 To UBound(strSuffixes)
         If Trim(strNames(i)) = strSuffixes(j) Then
          GetSuffix = strSuffixes(j)
          Exit Function
        End If
      Next j
    Next i
    End If
  End If
End Function

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Hi dhookom and MajP

Thank you for helping me separate my suffix. Do either of you do any freelance work, and if you do How do I contact you. I can’t find a way to send either of you a message on this sight.

Thanks -

TCB

 

[dhookom]

I don't do freelance and this site isn't the correct place to look for contractors. You can check the Microsoft MVP website.

Duane
Hook'D on Access
MS Access MVP