asked on Need help pulling last name from a string where it has two spaces?
Need help with Access VBA script that can determine the last name by identifying space in the end and only retrieving the last name in the full name.
So as an example: if the full name is John W Ciesle - how can I use InStr or InStrRev to give me the last name only as a value?
VBAMicrosoft Access
Last Comment
stephenlecomptejr
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
The down-side of this, or almost any other technique is where you have a split last name.
eg:
Van Cleafe
St. Pierre
Generally, when I'm splitting the last name, I will provide a way for the user to review the various elements of the name and confirm or potentially fix the splits.
eg:
Van Cleafe
St. Pierre
Generally, when I'm splitting the last name, I will provide a way for the user to review the various elements of the name and confirm or potentially fix the splits.
ASKER
Thank you for that Dale, what I've done is the following: (I just didn't post it to be precise on what I was asking:)
If CountofCharacterInString(sFullName, " ") = 2 And sFullName Like "* *" Then
'grab everything from space backward
'Stop
sFullName = Right$(sFullName, Len(sFullName) - InStrRev(sFullName, " "))
GoTo Exit_Proc
End If
Public Function CountofCharacterInString(sValue As String, sCharacter As String) As Long
On Error GoTo Err_Proc
Dim lCount As Long
lCount = Len(sValue) - Len(Replace(sValue, sCharacter, ""))
Exit_Proc:
CountofCharacterInString = lCount
Exit Function
Err_Proc:
Call LogError(Err, Err.Description, "sys_mTasks @ CountofCharacterInString")
Resume Exit_Proc
End Function
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dim arrNames() as string
arrNames = Split(Replace(FullName, " ", " "), " ")
LastName = arrNames(ubound(arrnames))
I've included a Replace() function in there to replace instances where there are multiple consecutive spaces (not uncommon with text strings) with a single space. Then use the Split() function to split the FullName into its parts, then select the last element of the array.
HTH