Instring Function Script

[Supermansteel]

I am trying to write this instring script on a text field on Customer Name and having difficulties with the last name:
list of customer names:
Bob A George
John Thompson
Paul B Michael

I know the instr function is having difficulties because some of the names have middle letters. For the ones that have a Middle letter it is giving me the results with a space before it _George and the ones that don't have a middle letter it is cutting off the first couple letters. or giving me the last name with the middle letter. I have been trying it all different kind of ways and haven't figure it out
I have tried formulas such as:
Right([CN],InStr([CN]," ")-1)

I have also tried a mid function and haven't been successful. I almost think I need to do an if statement and do it one way if there is a letter and another if it is false, but haven't figured out to write it. Any help on this would be greatly appreciated.

The Left([CN],Instr([CN]," ")-1) works fine for the first name.

Thanks,

Matt

[missinglinq]

LastName = Right([CN],Len([CN])-InstrRev([CN]," "))

Linq ;0)>

[Supermansteel]

LastName = Right([CN],Len([CN])-InstrRev([CN]," "))

Linq ;0)>

Wow that was alot easier than I anticipated and worked perfectly..


Thank you so much.

Matt

[ADezii]

Here is also a little Routine that I use to extract the First, Last, and possibly Middle Initials from a Name.

[missinglinq]

Here? Where?

Linq ;0)>

[NeoPa]

Leave him alone Linq!

He's having a little nap and will be back with some code shortly :D

[missinglinq]

I thought maybe it was some of that "stealth" code that can't be seen!

Linq ;0)>

[NeoPa]

No. I checked ;)

There was nothing else there :)

[ADezii]

==> Linq & NeoPa, you gentlemen do not have the necessary clearance to view it. Actually, I forgot to Post it and it's at work, but as soon as I get back to work, I'll 'try' again to remember to Post the code.

[missinglinq]

I understand that too much smoke eating can negatively affect long term memory!

Linq ;0)>

[ADezii]

I understand that too much smoke eating can negatively affect long term memory!

Linq ;0)>

Long, Short, and Intermediate! (LOL).

[ADezii]

Here is a little, General Template, (the one that NeoPa and Linq missed), which will parse a Full Name consisting of a First, Last, and Optional Middle Name/MI, into its constituent parts. It ignores Names with double spaces, Prefixes and Suffixes, Titles, etc.

Public Function fParseName(strName As String)
Dim varNameParts As Variant
Dim intCounter  As Integer
Dim strFirstName As String
Dim strLastName As String
Dim strMI As String
 
varNameParts = Split(strName)
 
Select Case UBound(varNameParts)
  Case 1    'First and Last Name
    strFirstName = varNameParts(0)
      Debug.Print strFirstName
    strLastName = varNameParts(1)
      Debug.Print strLastName
  Case 2    'First, MI, and Last Name
    strFirstName = varNameParts(0)
      Debug.Print strFirstName
    strMI = varNameParts(1)
      Debug.Print strMI
    strLastName = varNameParts(2)
      Debug.Print strLastName
 Case Else  'anything goes Dr. Tom Clancy III
   Debug.Print "Not a standard naming convention"
End Select
End Function

? fParseName("Alfred E. Neumann")
Alfred
E.
Neumann
 
? fParseName("Alfred Eugene Neumann")
Alfred
Eugene
Neumann
 
? fParseName("Dr. Thomas Cook III")
Not a standard naming convention

[NeoPa]

; - )

[Supermansteel]

I am going back to the drawing board on this one. I was just informed that the last name of this is not working correctly. Using this function:
LastName = Right([CN],Len([CN])-InstrRev([CN]," "))
It is Giving me the III or JR or SR instead. So if a name looks as such:
Alfred J Hitchcock Jr
Its giving me the Jr.
There were 5 instances that this happened in out of 200. Is this something I will need to do manually or is there a better way of writing this.

Please help,

Thanks,

Matt

[NeoPa]

You have to consider first what concept you will use to try to determine what should be recognised as the "Last Name". Usually, it's the last word in the field. If this is not right for you then you need to consider and specify, what logic you intend to use.

Turning the logic into code is only straightforward if the logic is known. Only you know your requirements.

[Supermansteel]

I need the First and Last names of the customer. When someone helped me with the last name I didn't think about the Jr or Sr or III part being on the end. If the very limited instances that this happens is there a way to write to code to reflect only the last name even if there is something such as Jr or Sr or II or III after there name?

[NeoPa]

That's what my last post was about.

In simpler terms that is something you will need to decide on yourself. We can help you code it when you know what you want, but we can't tell you what you want. That's always going to be down to you I'm afraid.

[Supermansteel]

I thought I was just doing that. My Customers Name comes in one field such as: Alfred J Hitchcock Jr. or Alfred Hitchcock or Alfred J Hitchcock.
I am using this for the first name: Left([CN],Instr([CN]," ")-1) which is working fine.
I now having difficulties with the last name. I just need the last name only. I do not need the middle initial (If there is one) or the ending part (Jr., Sr., III, etc). Is there a way to write it to only give me the Last Name?

Thanks NeoPA,

Matt

[NeoPa]

There still seems to ba a bit of confusion here Matt. Let me try to outline things a little.

Last Name is a concept that we can recognise quite easily as humans. We have learnt over many years how things fit together, and the rules for determining where to find this piece of info from within a larger "Name" format string. Because this is straightforward for us, doesn't mean that quite a lot of complex consideration is going on under the hood.

To translate this so that it can be processed by a computer is not remotely straightforward (due to the number of checks that DO go on under the hood if you think about it consciously). We are probably then looking at a set of imperfect rules to determine how best to approximate to a working solution. This might include numerous checks of the last "word" in the text to check for matching a known list of suffixes (Jr; Junior; II; III; IV; etc etc I'm sure you're getting the picture). It may alternatively, be a simpler algorithm which simply assumes that the last "word" is the last name.

How exactly do you want this to be done? Only you are in a position to make that call.

[Supermansteel]

NeoPa,
First of all, I wanted to thankyou for your time helping me with this. I understand the complexity of the name situation because the name is constantly different in every row. Some have middle intials, some don't, and some have endings such as Jr, Sr, III, etc.
If there is a way to extract out the Last Name only and not include the Jr, Sr, III, etc. I would greatly appreciate some help on how to do that.
If there is a much simplier means of getting the Last name and the ending part of Sr, Jr, III, etc, than that would be acceptable, however, I can't use it the way it is written right now because it is giving me only the very last thing in the Name Field and that information is useless to the people getting the data without the actual last name to go with the ending part.

Thanks,

Matt

[ADezii]

@Supermansteel
Here is some code, along with results, that will successfully generate the Last Name from any Name ending in Jr, Jr., Sr, Sr., II, III. It can be expanded as much as you like, and can easily be incorporated into the logic that I have previously demonstrated (Post #12). It is, as stated by NeoPa, almost impossible to allow for every contingency, however. Let me know if you need further explanation on the code.

Dim strFullName As String
Dim varNameParts As Variant
 
strFullName = "Alfred J Hitchcock Jr"
'strFullName = "Alfred J Hitchcock Jr."
'strFullName = "F. Wilder II"
'strFullName = "James Earl Jones III"
'strFullName = "Tom Jones Sr."
'strFullName = "Tom Jones Sr"
 
varNameParts = Split(strFullName, " ")
 
If InStr(strFullName, "Jr") > 0 Or InStr(strFullName, "Sr") Or _
   InStr(strFullName, "II") > 0 Or InStr(strFullName, "III") Then
  Debug.Print varNameParts(UBound(varNameParts) - 1)
End If

OUTPUT:

Hitchcock
Hitchcock
Wilder
Jones
Jones
Jones

[NeoPa]

OK. Let's get down to brass tacks.

There are two fundamental algorithms or approaches that can be used here. I was hoping that Matt could get that far by himself. At least to sit down and think about it and determine exactly what he was even looking for.

One is fairly simplistic and has already been attempted. Get the last word (set of characters that doesn't include a space) and assume it is the Surname required. This does have the drawback that the standard suffixes (Jr; Jr.; Junior; Sr; Sr.; Senior; I; II; III; IV; etc; etc; etc) are treated as the surname when present. This approach involves less up-front development, but many more false results.

The second is to follow the same logic except that this word is then checked against a known list of suffixes and, if a match is found, the previous word is then used. This approach involves much more development but far fewer failed results.

Assuming that what Matt really wants is the latter (probably a reasonable guess even without a direct statement to that effect) then ADezii has provided a solution that may fit the bill.

In a situation, such as this though, where the list of possible suffixes is so large, I would recommend using the software to find a match from within a table (If being done in Access this is straightforward. If in Excell then the data could be held in a (possibly hidden) worksheet and the VLookup() function used). This has the benefit of simplifying the code greatly. At least in as far as reading and understanding it goes. It also means that it should be a lot easier to add new items for exclusion when found.

[Stewart Ross]

With the excellent help provided by ADezii and NeoPa here there is little need for further comment I know, but taking a slightly different angle to my colleagues I'd just point out that trying to dis-assemble a name into its components is always a compromise. In mathematical terms there is no inverse function available for this task.

A name stored in a single field is in your case essentially made up of four components, two of which are optional:

Name = FirstName + [Initial] + LastName + [Honorific]

As the honorific component - the bit that identifies someone as Joe Bloggs Jr or Joe Bloggs III etc - is just a character sequence like the name itself there is no inverse function which can correctly return the last name in all circumstances without knowing all potential honorific values (now and in the future) in advance.

So, your single field is essentially acting as a function f(x) where x=F+I+L+H. You need to be able to return an inverse of this to separate L (or any other component) from the combined value. But this is not directly possible, as information about which of the components was entered has been lost by combining them as one field.

The look-up table as suggested by NeoPa is the best solution for this - but in my view your question is a very good illustration of why names should not be entered into single fields then parsed later. It is never 100% possible to recover the separate components in all circumstances and identify them 100% correctly as FirstName, LastName etc.

-Stewart

[OldBirdman]

None of the above examples even addresses the possibility of compound last names (surnames). Names containing de, van, von, Mc, Mac, will further compound any algorithm. When I have this problem, I count the words and when words <> 2, I open a dialog to ask the user to enter the surname.

OldBirdman

[ADezii]

NeoPa makes an excellent point in suggesting that if you really want to expand the logic as far as Prefixes and Suffixes go, you should test for Matches with a Table(s). If anyone is actually interested, I wrote a simple Algorithm that illustrates how this may be accomplished:

Public Function fParseFullName(strFullName As String)
Dim varNameParts As Variant
Dim blnHasPrefix As Boolean
Dim blnHasSuffix As Boolean
Dim MyDB As Database
Dim rstPrefixes As DAO.Recordset
Dim rstSuffixes As DAO.Recordset
 
Set MyDB = CurrentDb
Set rstPrefixes = MyDB.OpenRecordset("tblPrefixes", dbOpenForwardOnly)
Set rstSuffixes = MyDB.OpenRecordset("tblSuffixes", dbOpenForwardOnly)
 
'Initialize (not really required)
blnHasPrefix = False
blnHasSuffix = False
 
Do While Not rstPrefixes.EOF
  If InStr(strFullName, rstPrefixes![Prefix]) > 0 Then
    blnHasPrefix = True
      Exit Do
  Else
    rstPrefixes.MoveNext
  End If
Loop
 
Do While Not rstSuffixes.EOF
  If InStr(strFullName, rstSuffixes![Suffix]) > 0 Then
    blnHasSuffix = True
      Exit Do
  Else
    rstSuffixes.MoveNext
  End If
Loop
 
varNameParts = Split(strFullName, " ")
 
'UBound(varNameParts) can indicate the number of Spaces in a Name, so
'if a Name has a Prefix (blnHasPrefix = True) and a Suffix (blnHasSuffix = True),
'and also contains 4 Spaces it is more than likely:
'Prefix & First & MI & Last & Suffix and the First and Last Names will be
'contained in varNameParts(1) and varNameParts(3)
If blnHasPrefix And blnHasSuffix And UBound(varNameParts) = 4 Then
  Debug.Print "First Name: " & varNameParts(1)
  Debug.Print "Last Name: " & varNameParts(3)
End If
yada, yada, yada code...
 
 
rstPrefixes.Close
rstSuffixes.Close
Set rstPrefixes = Nothing
Set rstSuffixes = Nothing
End Function

Function Call and Results:

Call fParseFullName("Dr. Armund P. Dezii Sr.")

First Name: Armund
Last Name: Dezii

[OldBirdman]

I think you are giving false hope to Supermansteel. However this full name was generated, it was done by humans, and may have problems beyond honorifics, suffixes, middle names, initials, nicknames, or compound names. The simple addition of an extra blank destroys the accuracy of the function Split. There is a difference between "Howard James" and "Howard, James".

Either Supermansteel has some control over the input, and should therefore redesign the form and/or table, or he has no control, and should write some kind of parse routine and then manually either accept the result or re-enter the name field data.

You all can write very nice code to process any short sample data with 100% accuracy, providing you can see the sample data in advance. This gives the impression that the routine always works, and it won't. If the function call is:

Call fParseFullName("Dr. Armund P.  Dezii Sr.")

there will be no last name in varNameParts(3) and UBound(varNameParts) = 5, not 4.

Call fParseFullName("Armund Drew Dezii,  MD")

will meet your conditions, but First Name: "Drew" and Last Name: " ". Maybe yada, yada, yada code... will take care of this, but I doubt it.

OldBirdman

[ADezii]

@OldBirdman
You are, of course, correct in everything that you say. Actually, I was making a very silly point and stating that almost every conceivable contingency can be allowed for if you are willing to write enough code to allow for it. The revised code below will correctly process your 2 exceptions above, namely:
"Dr. Armund P. Dezii Sr." and "Armund Drew Dezii, MD".

Public Function fParseFullName(strFullName As String)
Dim varNameParts As Variant
Dim blnHasPrefix As Boolean
Dim blnHasSuffix As Boolean
Dim MyDB As Database
Dim rstPrefixes As DAO.Recordset
Dim rstSuffixes As DAO.Recordset
 
Set MyDB = CurrentDb
Set rstPrefixes = MyDB.OpenRecordset("tblPrefixes", dbOpenForwardOnly)
Set rstSuffixes = MyDB.OpenRecordset("tblSuffixes", dbOpenForwardOnly)
 
'Initialize (not really required)
blnHasPrefix = False
blnHasSuffix = False
 
strFullName = Replace(strFullName, "  ", " ")
strFullName = Replace(strFullName, ",", "")
 
Do While Not rstPrefixes.EOF
  If InStr(strFullName, rstPrefixes![Prefix] & " ") > 0 Then
    blnHasPrefix = True
      Exit Do
  Else
    rstPrefixes.MoveNext
  End If
Loop
 
Do While Not rstSuffixes.EOF
  If InStr(strFullName, " " & rstSuffixes![Suffix]) > 0 Then
    blnHasSuffix = True
      Exit Do
  Else
    rstSuffixes.MoveNext
  End If
Loop
MsgBox blnHasPrefix & " ==> " & strFullName & " ==> " & blnHasSuffix
varNameParts = Split(strFullName, " ")
 
'UBound(varNameParts) can indicate the number of Spaces in a Name, so
'if a Name has a Prefix (blnHasPrefix = True) and a Suffix (blnHasSuffix = True),
'and also contains 4 Spaces it is more than likely:
'Prefix & First & MI & Last & Suffix and the First and Last Names will be
'contained in varNameParts(1) and varNameParts(3)
If blnHasPrefix And blnHasSuffix And UBound(varNameParts) = 4 Then
  Debug.Print "First Name: " & varNameParts(1)
  Debug.Print "Last Name: " & varNameParts(3)
ElseIf Not blnHasPrefix And blnHasSuffix And UBound(varNameParts) = 3 Then
  'First & MI & Last & Suffix
  Debug.Print "First Name: " & varNameParts(0)
  Debug.Print "Last Name: " & varNameParts(2)
End If
 
rstPrefixes.Close
rstSuffixes.Close
Set rstPrefixes = Nothing
Set rstSuffixes = Nothing
End Function

P.S. - Keep in mind that what I have posted would only be a portion of the complete Algorithm.

[NeoPa]

These are all very valid points.

However, there are sometimes situations in the real world where a reasonable approximation is adequate, and indeed most appropriate. If you are receeiving millions of records of data and 99.99% of them can be handled by a certain algorithm, then it is reasonable to use that algorithm to process most of the data, but handle the rest by hand.

We cannot always exert control over incoming data. Hopefully, when we can, we do it intelligently. We cannot afford to refuse contemplation of issues simply because they are not formulated as we would have them. That way leads to sand in the ears (ask any ostrich) :D