Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create readable owner names (A2K SR1)

    I have a field titled Owner with data such as this:

    Smith, John
    Johnson, Jim H & Sally K
    Walters, Robert G & Betty R Tr
    Adams Trust
    Smith Industries
    Davidson, Dorothy R Trustee

    Most data is formatted like record 1. I use an Update Query to populate an empty field (PropertyOwner) by moving the last name to the end. The query searches based on the existence of a comma:

    IIf(InStr([Owner],","),Trim(Right(Trim([Owner]),Len(Trim([Owner]))-InStr(1,[Owner],","))) & " " & Trim(Left([Owner],InStr(1,[Owner],",")-1)),[Owner])

    However, the query produces poor results when it encounters the various versions of "trust"--Tr, Trustee, Trust.

    Can someone help me modify my query syntax to deal with these 3 versions of "trust" at the end of my owner names?

    I want my results to be:

    John Smith
    Jim H & Sally K Johnson
    Robert G & Betty R Tr
    Adams Trust
    Smith Industries
    Dorothy R Davidson Trustee

    Thanks for your help.

    Jeff Hamilton

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

    Re: Create readable owner names (A2K SR1)

    Probably, what you want can be done entirely using expressions in a query, but it would become difficult to understand and to maintain.

    I would create a function in a standard module that modifies the name, and then use this function in the update query.

    Here is an example. You can calll the function in your update query as ChangeName([Owner])

    Function ChangeName(varName)
    Dim intPosComma As Integer
    Dim intPosSpecial As Integer
    Dim i As Integer
    Dim strName As String
    Dim strSpecial As String
    Dim strEndingsArray(1 To 4) As String
    ' Get out if name is empty
    If IsNull(varName) Then
    Exit Function
    End If
    intPosComma = InStr(varName, ",")
    ' Return name unchanged if no comma
    If intPosComma = 0 Then
    ChangeName = varName
    Exit Function
    End If
    strName = varName
    ' Fill array
    strEndingsArray(1) = " Tr"
    strEndingsArray(2) = " Trust"
    strEndingsArray(3) = " Trustee"
    strEndingsArray(4) = " Industries"
    ' Loop through array
    For i = LBound(strEndingsArray) To UBound(strEndingsArray)
    intPosSpecial = InStr(strName, strEndingsArray(i))
    If intPosSpecial > 0 Then
    strSpecial = Mid$(strName, intPosSpecial)
    strName = Left$(strName, intPosSpecial - 1)
    Exit For
    End If
    Next i
    ' Build new name
    ChangeName = Mid$(strName, intPosComma + 2) & " " & Left$(strName, intPosComma - 1) & strSpecial
    End Function

    If the list of special cases becomes longer, I would suggest putting them in a table instead of in an array. Then you can open the table as a recordset and loop through it.

    HTH, Hans

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create readable owner names (A2K SR1)

    Thank you for your response. I've tried to use your code, but can't get it to work. I've never used VBA before. I entered the code in a Module window and saved the results. Question 1: Any idea why 2 module windows with seemingly identical contents opened up? I created a new Update Query and put the following phrase in the Update To: criteria--

    ChangeName([Owner])

    But when I ran the query I received an error that said--

    Undefined function "ChangeName" in expression.

    Question 2: What am I doing wrong?

    I have a module named ChangeName with your code in it. I have a Test table with 2 fields: Owner (populated with a few dummy records) and NewOwner (empty and waiting to be populated by the Update query). The Update Query only has the Test table associated with it. The update field is NewOwner.

    Thanks for your help.

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

    Re: Create readable owner names (A2K SR1)

    You need to change the name of the module. Access doesn't like it if a sub or function has the same name as a module.

    I think it should work then.

    Did you really get 2 separate windows with the same content, or did you accidentally split the window? In that case, you have one window with a horizontal bar in it - the splitter bar. Very useful to look at different parts of your module. You can get rid of it by dragging it to the top or bottom, or by double clicking in it.

    If you really got 2 windows, I have no idea what caused it. Hopefully just a temporary glitch.

    If you still have problems: I have attached a sample Access 97 database with a table, update query and module; you will have to unzip the database and convert it to A2K.
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Apr 2001
    Location
    California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create readable owner names (A2K SR1)

    Your sample database and module did the trick. I modified the code in the last line as follows--

    ChangeName = Trim(Mid$(strName, intPosComma + 1)) & " " & Trim(Left$(strName, intPosComma - 1)) & strSpecial

    That way it deals properly with data such as

    Smith,Jones
    Smith , Jones

    etc.

    Thanks very much for your help.

    Jeff

Posting Permissions

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