Dealing with Name data (Access 2002)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Perfect! Thanks so much for your response. You saved me lots of headaches.

Reply | Quote

Perfect! Thanks so much for your response. You saved me lots of headaches.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

>> 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 lazy to implement it…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

>> 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 lazy to implement it…

Reply | Quote

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.

Reply | Quote

You’re revised code worked perfectly. I tested it on a larger batch of data, and everything worked. It handled nulls and data that lacked commas just fine.

Thanks again to you and to Mark for taking the time and having the patience to help me. I really appreciate it.

Reply | Quote

You’re revised code worked perfectly. I tested it on a larger batch of data, and everything worked. It handled nulls and data that lacked commas just fine.

Thanks again to you and to Mark for taking the time and having the patience to help me. I really appreciate it.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote