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
27 3167 - LastName = Right([CN],Len([CN])-InstrRev([CN]," "))
Linq ;0)> - 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
Here is also a little Routine that I use to extract the First, Last, and possibly Middle Initials from a Name.
NeoPa 32,556
Expert Mod 16PB
Leave him alone Linq!
He's having a little nap and will be back with some code shortly :D
I thought maybe it was some of that "stealth" code that can't be seen!
Linq ;0)> NeoPa 32,556
Expert Mod 16PB
No. I checked ;)
There was nothing else there :)
==> 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.
I understand that too much smoke eating can negatively affect long term memory!
Linq ;0)>
I understand that too much smoke eating can negatively affect long term memory!
Linq ;0)>
Long, Short, and Intermediate! (LOL).
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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
@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 32,556
Expert Mod 16PB
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.
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
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
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
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
@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 32,556
Expert Mod 16PB
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Penn Markham |
last post by:
Hello all,
I am writing a script where I need to use the system() function to call
htpasswd. I can do this just fine on the command line...works great
(see attached file, test.php). When my...
|
by: Thomas Mlynarczyk |
last post by:
Hi,
I stumbled over a strange behaviour of Mozilla. When I want to access the
caller property of a function that was not called from within another
function, Mozilla seems to abort the script....
|
by: Mark Kurten |
last post by:
in the visual studio .NET samples that come with the product, why do
javascript functions all have the parameters below. Even though, the event
(like Onclick) which is calling it doesn't have any...
|
by: thisis |
last post by:
Hi All,
i have this.asp page:
<script type="text/vbscript">
Function myFunc(val1ok, val2ok)
' do something ok
myFunc = " return something ok"
End Function
</script>
|
by: yangsuli |
last post by:
i want to creat a link
when somebody click the link
the php script calls a function,then display itself :)
i have tried
<a href=<? funtion(); echo=$_server ?>text</a>
but it will call the...
|
by: Larax |
last post by:
Best explanation of my question will be an example, look below at this
simple function:
function SetEventHandler(element)
{
// some operations on element
element.onclick =
function(event)
{
|
by: WGW |
last post by:
Hello all, I need another set of eyes cause it just isn't working, no matter how identical I make it.
The initRotator function works when called by itself, but when adding another function, only the...
|
by: Hongyu |
last post by:
Hi,
I have a datetime char string returned from ctime_r, and it is in the
format like ""Wed Jun 30 21:49:08 1993\n\0", which has 26 chars
including the last terminate char '\0', and i would...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |