Splitting Names using a Module 2

[kerenjamieson]

I have a table called tbl_imported_data which contains one field called 'Contact Names'. Contact Names consists of data such as Mr. G Jamieson, Gary Jamieson, Gary R. Jamieson etc

I want to append this imported data to another table called tbl_names with the fields Salutation, First, Middle and Last.

As this is an everyday job (albeit tedious), is there a way of creating a module which could 'automatically' append contact names from the imported table into the correct fields of the names table?

I had a look at one of the threads (by jimmythegeek) relating to parsing a full name, but, being a 'beginner' at VBA I'm quite confused and unsure where to start.

Can the whole name be split automatically?
How do you 'call' the module for it to do what I want?

Any help would be appreciated.

Keren

 

[GHolden]

As there is no fixed format for the names in you table it's going to very difficult (if not impossible) to split them using code. If they were all of the same format

eg. "Mr J Smith"
"Miss Joan Smith"
"Mrs J Smith"

You could split them faily easily by checking for spaces in the string.

As they are all mixed up, I can't see a way that you can tell which part of the string is which part of the name.

ie. The first part could be Salutation, first name or first initial.

It would probably be a good idea to enforce formatting of names entered into the table from now on to avoid this problem.

Good Luck

 

[scriverb]

Here is demo on how to do what you want. We will have to modify it to perform the update function but this one just takes a full name as you describe and splits it up into individual fields. Take a look and see if it will work for you. I can help you implement it into your system with more info.

I created a table called tblContacts with the following fields: Name, Salutation, FirstName, Middle, LastName. Now enter a bunch of name combinations that you would have in your database. then put the following code behind a button on a new form in the OnClick event procedure.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vSalutation As String, vFirstName As String, vMiddle As String, vLastName As String, vName As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblContacts", dbOpenDynaset)
rs.MoveFirst
Do
vName = rs("Contact Names&quot
If InStr("Mr.,Mrs.,Ms.,Mr,Mrs,Ms,Miss,Master,Dr.,Dr", Mid$(vName, 1, InStr(1, vName, " &quot - 1)) > 0 Then
vSalutation = Mid$(vName, 1, InStr(vName, " &quot - 1)
vName = Mid$(vName, InStr(vName, " &quot + 1)
rs.Edit
rs("Salutation&quot = vSalutation
rs.Update
End If
vFirstName = Mid$(vName, 1, InStr(vName, " &quot - 1)
vName = Mid$(vName, InStr(vName, " &quot + 1)
rs.Edit
rs("FirstName&quot = vFirstName
rs.Update
If InStr(Trim(Mid$(vName, 1)), " &quot = 0 Then
vMiddle = " "
vLastName = Trim(Mid$(vName, 1))
Else
vMiddle = Trim(Mid$(vName, 1, InStr(vName, " &quot - 1))
vName = Mid$(vName, InStr(vName, " &quot + 1)
vLastName = Trim(Mid$(vName, 1))
End If
rs.Edit
rs("Middle&quot = vMiddle
rs("LastName&quot = vLastName
rs.Update
rs.MoveNext
Loop Until rs.EO

Run the code and see if you like the results. Bob Scriver

 

[JoyInOK]

This will be a tedious coding job, but at least once you coded and debugged it, you wouldn't have to do the tedious job over and over again like you do now.
GHolden is right, if you can possibly adapt the program gathering the info so it conforms to your table's needs, that would be the easiest solution.
You call a procedure from vba code by whatever name you chose when writing it. First, create the function/s (since you send data to the procedure and expect to get data back, a function would work best.

Function GetSalutation(byRef strIncomingName as string ) as String

Here, the function name is "GetSalutation"
"(strIncomingName as string)" tells what data is being sent to the function when it is called. this is called a parameter. You can have more than one parameter, separated by commas.
"as String" defines the type of data to be sent back to the calling procedure.
When you call the procedure from within another procedure, it will look something like this:
dim strCurrentName as string
strCurrentName = txtName.text
dim strSalutation as string
strSalutation = GetSalutation(strCurrentName)

strSalutation would be set to any Mr., Mrs. Dr. etc. because your code in the function would look for these.
Sending the paramater By Reference means that the function can change the variable sent to it, and those changes would be reflected in the calling procedure. In this case, you could remove the saluation once you found it.
The rest of the procedure could call additional functions with that same changed variable (without the salutation) to pull out any suffix (Jr., Sr. III), middle initial (a single letter followed by a period), the last name if it's first (you would know because there's a comma in it), the last name if it's last (no comma, so the beginning of the last name starts after the first blank space from the end)
the first name (the first letter to the first blank space), and finally, a full middle name. Each successive function will search for its target, then remove it from the string to make looking for the next target easier.
If you are completely new to VBA, you'll need to take a course (online or off) or get a beginning VBA book and read through it. The string manipulation tools you need should be available in any beginning course. If you get stuck, come back with your code and we'll try to help you through it.

 

[JoyInOK]

Way to have some code ready, Bob! But looks like your solution only works on fields where the info is in Salutation, FirstName, MiddleName, LastName order. You'll need a lot more manipulation with the non-standard order.

 

[scriverb]

JoyInOK: Sorry about that, but there never was mention that there was going to be scrambled data order in the name field. There has to be some order or you have an impossibile task. There must be rules for you to be able to parse the string of data. If there is going to be names like Smith, Robert M. then someone should say that. But, the example that was presented showed names in order by salutation, first, middle, last.

Why don't we try to parse a coded message without the keycode to unscramble it first. If the data is in absolutely no predetermined order by rather random how do you expect to parse the first name from the middle name or the last name.

We can add the Jr., Jr, III, IV, V suffex easy enough but that is only a slight modification.

Bob Scriver

 

[scriverb]

UPDATE: Here is an adjustment to parse off the suffix(Jr., III, II etc). Add another field called Suffix to the table to get this to work for you. This will work in most cases as long as the names were entered in the order you described left to right salutation, first, middle, last, suffix. You will have to handle the odd name maybe by hand especially when you can't control the keyed input.

Dim db As Database
Dim rs As Recordset
Dim vSalutation As String, vFirstName As String, vMiddle As String, vLastName As String, vName As String, vSuffix As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblContacts", dbOpenDynaset)
rs.MoveFirst
Do
vName = rs("Contact NameS&quot
If InStr(1, "Mr.,Mrs.,Ms.,Mr,Mrs,Ms,Miss,Master,Dr.,Dr", Mid$(vName, 1, InStr(1, vName, " &quot - 1)) > 0 Then
vSalutation = Mid$(vName, 1, InStr(vName, " &quot - 1)
vName = Mid$(vName, InStr(vName, " &quot + 1)
rs.Edit
rs("Salutation&quot = vSalutation
rs.Update
End If
vFirstName = Mid$(vName, 1, InStr(vName, " &quot - 1)
vName = Mid$(vName, InStr(vName, " &quot + 1)
rs.Edit
rs("EditedName&quot = vName
rs("FirstName&quot = vFirstName
rs.Update
If InStr(1, Trim(Mid$(vName, 1)), " &quot = 0 Then
vMiddle = " "
vLastName = Trim(Mid$(vName, 1))
Else
vMiddle = Trim(Mid$(vName, 1, InStr(vName, " &quot - 1))
vName = Mid$(vName, InStr(vName, " &quot + 1)
End If
If InStr(1, vName, " &quot > 0 Then
If InStr(1, "Jr.,Jr,II,III,IV,V,VI,VII,VIII,Esq.,Esq", Mid$(vName, InStr(1, vName, " &quot + 1)) > 0 Then
vLastName = Trim(Mid$(vName, 1, InStr(vName, " &quot - 1))
vName = Mid$(vName, InStr(vName, " &quot + 1)
vSuffix = Trim(vName)
Else
vLastName = Trim(vName)
vSuffix = " "
End If
Else
vLastName = Trim(vName)
vSuffix = " "
End If
rs.Edit
rs("Middle&quot = vMiddle
rs("LastName&quot = vLastName
rs("Suffix&quot = vSuffix
rs.Update
rs.MoveNext
Loop Until rs.EOF

Good luck.

Bob Scriver Bob Scriver

 

[kerenjamieson]

Thank you for the suggestions and code. I'm going to look at this today and hopefully move forward on it.

Keren

 

[GHolden]

Excellent code scriverb, have a star!

 

[scriverb]

Thanks GHolden. It was a fun project. I have parsed many name fields in a host of different formats so it was just a matter of stepping through possibilities from left to right. I also have an InstrRight function that I wrote that searches for a string combination from the right rather than from the left that helps sometimes. Bob Scriver

 

[GHolden]

Could you post the InstrRight code also, as I would be interested to see how you achieved this.

Thanks

 

[JoyInOK]

You're right, Bob. I read the second line of example names as "Jamieson, Gary" and thought we had names in any kind of order. You get another star for reading the question correctly!
Having worked with a bunch of non-standardized name fields, I still think it could be parsed out, first by pulling out the obvious suffixes, then the obvious salutations, then any middle initial (a lone character surrounded by blank space or followed by a period) then the last name (either whatever's in front of the comma, if there is one, or whatever's after the last blank space). What's left will be the first name, or if there's a blank in there, the first name and the middle name.
It would be a handy procedure for people trying to normalize poorly constructed databases. I may work on it and post an FAQ when I get it finished.

 

[ClydeDoggie]

As regards the InStrRight code, you might want to know it already exists in VBA (ver.6) as InStrRev. Another useful string function is Split(expression,delimeter). It returns a string array of the elements in expression using delimeter as the break. ie: Split("The answer is"," &quot returns a three element array of "The", "answer", "is".
Regarding Name parsing in general, I have found it useful to remove all punctuation before starting so you don't get a last name of Smith, from Allan Smith, Jr. Beware the politically correct use of two last names though (Joe Smith-Jones). If you are down south you might have problems with something like Billy Bob Thornton, where he would like Billy Bob as his first name. All in all Name parsing is full of traps so I would build in some review function to whatever solution you come up with.

 

[scriverb]

JoyInOK: Thanks for the Star. I agree that we can write solution to just about anything in ACCESS. The issue that the user has to live with is that in instances where there is no set formating the probability of 100% success in parsing the names out starts to slide. In your example there are instances that would cause data to be dropped into the wrong field. (i.e. Billy Joe Michael Mac Nully III - even my procedure will butcher this one. Is it Billy Joe(first name) or Billy(first name) and Joe Michael(Middle name) It is never going to be perfect but we can usually do it a whole lot faster and at about 98% and let the user figure out the others.

GHolden: Actually I have two functions that assist in searching from the right. InStrRight and InStrRev

InStrRight requires the passing of two parameters. First parameter is the String being searched. Second is the Target String or the string being searched for. It returns a Long integer value that represents the character postition of the first character of the Target String in relationship to the left most character of the Search String It searches starting from the right most character of the Search String until it either finds the Target String or checks the left most character. If it finds the Target String it returns a character postion. If not it returns a Zero(0).

Public Function InStrRight(vSearchStr As String, vTargetStr As String) As Long
Dim i As Integer
For i = (Len(vSearchStr) - Len(vTargetStr)) To 1 Step -1
If InStr(i, vSearchStr, vTargetStr, 1) = i Then
InStrRight = i 'Position of vTargetStr
Exit For
End If
Next i
If InStrRight <> i then InStrRight = 0
End Function

The InStrRev works the same way but it has three parameters to be passed. First is the Starting position, Second is the Search String, and Third is the Target String. Unlike the InStrRight this function can start at some predetermined position in the string. Say character 23 out of 40 and starts looking from right to left to find the Target String.

Public Function InStrRev(vStartPoint As Integer, vSearchStr As String, vTargetStr As String) As Long
Dim i As Integer
For i = vStartPoint To 1 Step -1
If InStr(i, vSearchStr, vTargetStr, 1) = i Then
InStrRev = i
Exit For
End If
Next i
If InStrRev <> i then InStrRev = 0
End Function

I hope these can help you. Bob Scriver

 

[scriverb]

ClydeDoggie: As you can see from my post I had to develop my own InStrRev but I am really interested in the Split Function. Looking forward to the upgrade.

You are absolutely correct concerning the punctuation problems. Usually I do that but didn't address that in this post. I think there was a PunctuationStrip function posted by someone back a while. If I can find it I will post it here so that kerenjamieson can use it also.
Bob Scriver

 

[GHolden]

Nice one scriverb. I have a function for removing punctuation. It does fall over if you have &quot; in the the string but for what I was doing this wasn't a problem.


Public Function gRemovePunctuation(ByVal vstrText As String) As String
Dim strNewText As String
Dim intTextCount As Integer
Dim intTextLen As Integer
Dim intNext As Integer

On Error GoTo ErrorHandler

intTextLen = Len(vstrText)
For intTextCount = 1 To intTextLen
intNext = Asc(Mid(vstrText, intTextCount, 1))
If (intNext > 47 And intNext < 58) Or (intNext > 64 And intNext < 91) Or (intNext > 96 And intNext < 123) Or (intNext = 32) Then
strNewText = strNewText & Chr(intNext)
End If
Next intTextCount

gRemovePunctuation = strNewText

Exit Function

ErrorHandler:

MsgBox Err.Description

End Function