Avatar of stephenlecomptejr
stephenlecomptejr
Flag for United States of America 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

Avatar of undefined
Last Comment
stephenlecomptejr

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Fabrice Lambert

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
Sign up - Free for 7 days
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
Dale Fye

or:
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

Dale Fye

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.


stephenlecomptejr

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

Open in new window


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


Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck