Create readable owner names (A2K SR1)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote