INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Move from Name Field to Suffix feild

Move from Name Field to Suffix feild

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

RE: Move from Name Field to Suffix feild

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

Duane
Hook'D on Access
MS Access MVP

RE: Move from Name Field to Suffix feild

CODE

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; 
give you something like

CODE

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

CODE

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

CODE

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

CODE

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 

RE: Move from Name Field to Suffix feild

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

RE: Move from Name Field to Suffix feild

(OP)
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/getfile.aspx?folder=5...

TCB

RE: Move from Name Field to Suffix feild

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

CODE --> vba

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

RE: Move from Name Field to Suffix feild

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close