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?
-
-
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.Comment
-
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,
MattComment
-
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.Comment
-
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,
MattComment
-
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.
OUTPUT: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
Code:Hitchcock Hitchcock Wilder Jones Jones Jones
Comment
-
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.Comment
-
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.
-StewartComment
-
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.
OldBirdmanComment
-
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:
Function Call and Results:Code: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
Code:Call fParseFullName("Dr. Armund P. Dezii Sr.")Code:First Name: Armund Last Name: Dezii
Comment
-
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:
there will be no last name in varNameParts(3) and UBound(varNameP arts) = 5, not 4.Code:Call fParseFullName("Dr. Armund P. Dezii Sr.")
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.Code:Call fParseFullName("Armund Drew Dezii, MD")
OldBirdmanComment
-
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".
P.S. - Keep in mind that what I have posted would only be a portion of the complete Algorithm.Code: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 FunctionComment
-
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) :DComment
Comment