Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dealing with Name data (Access 2002)

    I'm trying to copy data from a field Owner into a new field PropertyOwner. The data is not consistent. Some is in this form: "HAMILTON, JEFF". Some leaves out the space after the comma. Some records have no comma at all. My biggest headach, though, is dealing with all the variations of TRUST, LIVING TRUST and TR in the data. If I have a record that looks like this--"HAMILTON,TR"--it needs to come like "HAMILTON TR". I've attached a zip file that contains a test table, update query and module that I think is close to what I need. It doesn't deal properly with all the instances of the data. If someone can take a look and help fix my query or the module, or suggest some other alternative, I'd appreciate it.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dealing with Name data (Access 2002)

    Long time no see, Jeff!

    Try the code in the attached text file. It works correctly for the table in the database you posted, but stress test it before using it in real life.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dealing with Name data (Access 2002)

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Perfect! Thanks so much for your response. You saved me lots of headaches.

  4. #4
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dealing with Name data (Access 2002)

    I spoke a bit too soon. When I tried it on a larger set of data, I noticed a couple of problems. First, the routine doesn't deal properly with the following data: JOSLIN,BARBARA A ETAL. I get the following result: JOSLINE BARBARA A ET AL when it should be BARBARA A JOSLIN ETAL. I think we need to add a procedure that searches for the unique endings (TR, ETAL) and stores that to a variable, say ENDING. Then if there's a comma, the string to the left of the comma gets stored to a variable LASTNAME. Then we store the string data to the right of the comma to a variable up to but excluding the unique ending string, if it exists, and call that FIRSTNAME. Then we rebuild the name as FIRSTNAME &" "& LASTNAME& " "& ENDING. I don't know how to code this and don't know if I'm on the right track, but these are my thoughts.

    Second, the procedure doesn't deal properly with last names that begin with TR: AGUILAR,TRINIDAD or BRISENO,TRIFUNO & ANA TR (the last one's really nasty). If we could deal with the first of the two it would be great. I can accept that a canned routine probably can't deal with all possible versions of the data.

    I've attached another copy of the database. Note that I added lines to the code trying to cover more versions of the ending, but it didn't seem to help.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dealing with Name data (Access 2002)

    Name parsing generally isn't intended to deal with entities like trusts, joint accounts and (Et Al) groups. You will have to use special handling for those entries.
    Charlotte

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Dealing with Name data (Access 2002)

    If interested, you can try using these functions:

    Public Function GetNewText(ByVal strTxt As String) As String
    On Error GoTo Err_Handler

    Dim strMsg As String
    Dim intPos As Integer
    Dim strTmp As String
    Dim strFName As String
    Dim strLName As String
    Dim strSuffix As String

    If InStr(1, strTxt, ",", 0) > 1 Then
    intPos = InStr(1, strTxt, ",", 0)
    ElseIf InStr(1, strTxt, " ", 0) > 1 Then
    intPos = InStr(1, strTxt, " ", 0)
    Else
    ' No commas or spaces:
    GetNewText = strTxt
    Exit Function
    End If

    strLName = Left$(strTxt, intPos - 1)
    strTmp = Mid$(strTxt, intPos + 1)

    Do
    intPos = InStr(1, strTmp, " ", 0)
    If intPos = 0 Then
    If IsSuffix(strTmp) Then
    strSuffix = strTmp
    Else
    strFName = strFName & " " & strTmp
    End If
    Exit Do
    Else
    If IsSuffix(strTmp) Then
    strSuffix = strTmp
    Exit Do
    Else
    strFName = strFName & " " & Left$(strTmp, intPos - 1)
    strTmp = Mid$(strTmp, intPos + 1)
    End If
    End If
    Loop

    GetNewText = Trim$(strFName & " " & strLName & " " & strSuffix)

    Exit_Sub:
    Exit Function
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Sub

    End Function

    Private Function IsSuffix(ByRef strTmp As String) As Boolean

    strTmp = UCase$(strTmp)

    Select Case strTmp
    Case "ETAL", "ET AL", "TR", "TR ET AL", "TR ETAL", _
    "TRUST", "TRUST ET AL", "TRUST ETAL", _
    "LIVING TR", "LIVING TR ET AL", "LIVING TR ETAL", _
    "LIVING TRUST", "LIVING TRUST ET AL", "LIVING TRUST ETAL"
    IsSuffix = True
    Case Else
    IsSuffix = False
    End Select

    End Function

    In test query, the GetNewText function returned correct results, using the table with sample data provided in your attachment. But if there are even more exclusions, exceptions, and deviations not included in the sample table, you will have to modify functions above accordingly. If modifying function, ensure that there will always be exit point for the Do loop so it does not loop endlessly. Also if any null fields in table use Nz function in query or else function will result in Invalid Use of Null error.

    HTH

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dealing with Name data (Access 2002)

    MarkD has posted an elegant solution. Here is my rather kludgy adaptation of the original code (attached as text file.) If you have more special cases, you will have to add them, starting with the longest and working towards the shortest.
    Attached Files Attached Files

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Dealing with Name data (Access 2002)

    Hans you could put them into a table and sort them in descending order on the length of the field.

    What do you mean kludgy, kludgy is in the eye of the beholder.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dealing with Name data (Access 2002)

    >> you could put them into a table and sort them in descending order on the length of the field

    Yes, I thought of that, but I was too <img src=/w3timages/censored.gif alt=censored border=0> lazy to implement it... <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Dealing with Name data (Access 2002)

    Just like my old maths teacher in year 11. He as of the opinion that 'mathmeticians are lazy, boys' and I'm am going to teach you the short (as well as the long) way of solving maths problems. We were the 5B class and so he tookit as a bit of a challenge to outdo the 5A class. To cut a long story short, he did so easily.

    Enough of this nostalgia, still a nice solution Hans.

  11. #11
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dealing with Name data (Access 2002)

    Is such special handling something best left to an accomplished programmer (something I'm not) or are the solutions simple enough that I can understand and implement them? BTW, I have to deal with close to 40,000 records, of which a few hundred change each month. There's no easy way to know each month which records may have these unique naming characteristics.

  12. #12
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dealing with Name data (Access 2002)

    Thanks very much for your code example. Would all this be in one module? Is that module of VBA what is known as a user defined function?

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Dealing with Name data (Access 2002)

    Attached is modified version of the db you attached previously. The code I used is in Module1. The GetNewText function is declared as Public so it can be used in a query. Any function you write yourself, as opposed to the built-in functions provided by Access or by VBA, is usually described as a "user-defined" or "custom" function. For example of use in query, see "Test Query" and "Update Test Query" (update query) in attached db. If the data being processed each month is in same general format, it would not be hard to modify IsSuffix function Select Case statement to include other possibilities, but if the data is radically different there may be no single function that could effectively handle every possible case.

    HTH
    Attached Files Attached Files

  14. #14
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dealing with Name data (Access 2002)

    Thanks very much for your help. I'll give your code a try.

  15. #15
    New Lounger
    Join Date
    Feb 2004
    Location
    Glendale, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dealing with Name data (Access 2002)

    Unfortunately, your code did not deal well with null values and names that had no commas. I know you advised me to use the Nz function in the query, but I don't know what that is or how to use it.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •