UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Extract Lastname, Firstname From String, Access 2010    
 
   
Cdex
post Sep 11 2017, 05:06 PM
Post#1



Posts: 44
Joined: 29-December 13



Hello,

The name field I'm working with has several variations of Last, First Middle:

Last, First MI
Last, First MiddleName
Last, First MiddleName AnotherMiddleName

No separators other than , and single space

I need to extract last, first

Have tried multiple variations of Right, Trim, Len, Instr with no luck.

Thanks very much.






Go to the top of the page
 
DanielPineault
post Sep 11 2017, 05:17 PM
Post#2


UtterAccess VIP
Posts: 5,154
Joined: 30-June 11



Extracting the last name is easy

CODE
Left([YourFieldName],InStr([YourFieldName],",")-1)


The issue however is the firstname. There are too many variation to be able to extract it reliably, but you could try something along the lines of

CODE
Left(Trim(Mid([YourFieldName],InStr([YourFieldName],",")+1)),InStr(Trim(Mid([YourFieldName],InStr([YourFieldName],",")+1))," ")-1)


The issue arise with compound names such as Van der Poorten, Anna Maria, Mary Anne, Sarah Jane ...

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
mklein
post Sep 11 2017, 07:00 PM
Post#3



Posts: 225
Joined: 7-August 12
From: BC, Canada


I would use the Split() function, like...
CODE
Private Sub test01274148()
    Const TEST As String = "Last, First MiddleName AnotherMiddleName"
    Dim vNames
    Dim i As Integer

    vNames = Split(TEST)

    Debug.Print "LastName: " & Replace(vNames(0), ",", "")
    Debug.Print "FirstName: " & vNames(1)
    For i = 2 To UBound(vNames)
        Debug.Print "Middle Name or Init " & i - 1 & ": " & vNames(i)
    Next
End Sub

hth
Mark

--------------------
| Mark Klein | Access 2010 | Windows 10 | Visual Studio 2013
Go to the top of the page
 
BruceM
post Sep 12 2017, 07:40 AM
Post#4


UtterAccess VIP
Posts: 6,832
Joined: 24-May 10
From: Downeast Maine


Assuming the last name is always followed by a comma, Daniel's version offers the advantage of parsing something like "Van Helsing, Abraham" properly in terms of the last name. For compound first names (Billy Bob, Mary Jane, etc.) it will likely be necessary to do some manual rearrangment. For the part after the comma you could use Split, or something like Daniel's suggestion to do most of the work. Depending on the origin of the list you may know that there are no compound first names, which would simplify the task. The best approach will depend on the details of the data.

Untested procedure follows. In query design view:

LastName: ParseName([NameField],"L")

And so forth for first name, middle name, and additional middle names

CODE
Public Function ParseName(ByVal NameText As String, ByVal NamePart As String) As String

'  NamePart is "L" for last name, "F" for first name, "M" for middle name, "M2" for additional middle names
  
  Dim strNotLast As String  ' The portion of NameText after the comma
  Dim intSpaces  As Integer  ' The number of spaces after the comma
  Dim intLeft   As Integer  ' For names with more than one middle name: Length of first + length of middle + space between first and middle + space after middle

  strNotLast = Trim(Split(NameText,","))
  intSpaces = UBound(Split(strNotLast," "))

  Select Case NamePart
    Case "L"
      ParseName = Left(NameText,InStr(NameText,",") - 1
    Case "F"
      ParseName = Split(strNotLast," ")(0)
    Case "M"
      If intSpaces > 0 Then
          ParseName = Split(strNotLast," ")(1)
      End If
    Case "M2"
      If intSpaces > 1 Then
          intLeft = Len(Split(strNotLast," ")(0)) + Len(Split(strNotLast," ")(1)) + 2
          ParseName = Mid(strNotLast,intLeft)
      End If
    Case Else
      ParseName = ""
  End Select

End If


Go to the top of the page
 
Cdex
post Sep 12 2017, 03:11 PM
Post#5



Posts: 44
Joined: 29-December 13



Thanks Daniel. Both work well except for records with no middle name or initial. (returns #Func!) Any suggestions for an iif statement to accommodate these?
Go to the top of the page
 
doctor9
post Sep 12 2017, 05:01 PM
Post#6


UtterAccess Editor
Posts: 17,609
Joined: 29-March 05
From: Wisconsin


Cdex,

Give this a try. It's based on Daniel's code, but adds IF tests to see if there's a name portion to return before trying to return it.

CODE
Public Function ParseName(NameText As String, NamePart As String) As String

'  NamePart is "L" for last name, "F" for first name, "M" for middle name, "M2" for additional middle names
  
'   Example NameText values:
'   Last, First
'   Last, First MI
'   Last, First MiddleName
'   Last, First MiddleName AnotherMiddleName
  
    Dim strNames() As String, i As Integer, intNumNames As Integer
    
'   Ignore blank/Null names
    If Len(NameText & "") = 0 Then Exit Function
    
'   Parse each "word" as a separate name in an array
'   (replace the comma as a space so we can do this without worrying about LName,FName issues)
    strNames = Split(Replace(Replace(NameText, ",", " "), "  ", " "), " ")
    
'   Get the count of names (starting at 0 because that's how Split works)
    intNumNames = UBound(strNames)
    
'   Only return a value if we have something to return
    Select Case NamePart
    Case "L"    'Last name
        ParseName = strNames(0)
    Case "F"    'First name
        If intNumNames > 0 Then ParseName = strNames(1)
    Case "M"    'Middle name
        If intNumNames > 1 Then ParseName = strNames(2)
    Case "M2"   'Additional middle name(s)
        If intNumNames > 2 Then
'           Just in case the name is "Smith, Billy Bob Ray Joe Stanley" let's just loop through
'           all of the names after the first middle name.
            For i = 3 To intNumNames
                ParseName = ParseName & strNames(i) & " "
            Next i
        End If
    Case Else
        ParseName = "Unknown name portion '" & NamePart & "'"
    End Select

End Function

Hope this helps you to realize that it's a bad idea to store multiple name parts in a single field. Access records/fields are meant to be Atomic - one piece of information. After you parse these out, if you can, try to alter the table/data entry method so new records won't have this problem.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Cdex
post Sep 12 2017, 05:19 PM
Post#7



Posts: 44
Joined: 29-December 13



Hi Dennis - I always normalize, but in this case I'm dealing with data pulled from two other systems and neither have normalized employee names. I need to compare values, so I figured the easiest path was to remove middle names from List A so it would match List B's format (last, first). I'll give your code a try. Thanks!
Go to the top of the page
 
BruceM
post Sep 13 2017, 07:08 AM
Post#8


UtterAccess VIP
Posts: 6,832
Joined: 24-May 10
From: Downeast Maine


Hi Dennis,

Actually, that was my code, with the last name portion derived from Daniel's code. I used the comma to identify the last name because all of the sample data indicated a comma following the last name, so I started from that assumption. That the names are not normalized in the various systems suggests a modified approach may be needed depending on the actual format of the names. Absent that information, I based the code on the available information, and designed it so the portion in front of the comma is handled as a last name. In other words, Van Helsing, Abraham renders as FirstName: Abraham, LastName: Van Helsing instead of FirstName: Helsing, MiddleName: Abraham, LastName: Van.

I had a chance to run the code today, and discovered a couple of errors (including End If instead of End Function). Here it is with the fixes:

CODE
Public Function ParseName(ByVal NameText As String, ByVal NamePart As String) As String

'  NamePart is "L" for last name, "F" for first name, "M" for middle name, "M2" for additional middle names
  
  Dim strNotLast As String   ' The portion of NameText after the comma
  Dim intSpaces  As Integer  ' The number of spaces after the comma
  Dim intLeft    As Integer  ' For names with more than one middle name: Length of first + length of middle + space between first and middle + space after middle

  strNotLast = Trim(Split(NameText, ",")(1))
  intSpaces = UBound(Split(strNotLast, " "))

  Select Case NamePart
    Case "L"
      ParseName = Left(NameText, InStr(NameText, ",") - 1)
    Case "F"
      ParseName = Split(strNotLast, " ")(0)
    Case "M"
      If intSpaces > 0 Then
          ParseName = Split(strNotLast, " ")(1)
      End If
    Case "M2"
      If intSpaces > 1 Then
          intLeft = Len(Split(strNotLast, " ")(0)) + Len(Split(strNotLast, " ")(1)) + 2
          ParseName = Mid(strNotLast, intLeft + 1)
      End If
    Case Else
      ParseName = ""
  End Select

End Function


In my testing it worked as intended, assuming the last name is followed by a comma. If it is not, presumably it is First Middle Last. The code could be adapted for that contingency, but as I mentioned I based the code on the information provided.

You could have the function return a Variant if you would prefer null to a zero-length string if there is no middle name or "extra" middle name.

As for the #Func error, please describe the conditions that produce the error.
Go to the top of the page
 
Cdex
post Sep 13 2017, 08:08 AM
Post#9



Posts: 44
Joined: 29-December 13



First - thanks to all of you for taking time to address this. #func! pops up when the field has no middle name, middle initial, or compound first name... [Lastname, Firstname] only.
Go to the top of the page
 
doctor9
post Sep 13 2017, 08:10 AM
Post#10


UtterAccess Editor
Posts: 17,609
Joined: 29-March 05
From: Wisconsin


Cdex,

That's odd. When I test it in the immediate window like this:

CODE
? ParseName("Smith, Bob","M")


It just returns an empty string. Where exactly are you seeing "#func!"? Can you give a specific example of input that results in this happening, just in case I'm making a false assumption somewhere?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
BruceM
post Sep 13 2017, 08:57 AM
Post#11


UtterAccess VIP
Posts: 6,832
Joined: 24-May 10
From: Downeast Maine


Which version of the code are you using? I am not getting the #Func error using my latest version of the code. I don't know which version Dennis used, but nor is he getting the error. So it would help to know exactly what data and actions lead to the error.

Another question: Is the last name followed by a comma in all of the records?
Go to the top of the page
 
Cdex
post Sep 13 2017, 11:12 AM
Post#12



Posts: 44
Joined: 29-December 13



#Func! appears as the value retrieved when I use (Daniel's) line below as an expression in a query. Other than this, I have what I need.

Left(Trim(Mid([YourFieldName],InStr([YourFieldName],",")+1)),InStr(Trim(Mid([YourFieldName],InStr([YourFieldName],",")+1))," ")-1)

Yes, all last names are followed by a comma.



Go to the top of the page
 
Cdex
post Sep 13 2017, 11:26 AM
Post#13



Posts: 44
Joined: 29-December 13



Got it. Bruce's code posted today does it! Thanks very much!
Go to the top of the page
 
BruceM
post Sep 13 2017, 11:42 AM
Post#14


UtterAccess VIP
Posts: 6,832
Joined: 24-May 10
From: Downeast Maine


We are all glad to help. Good luck with the project!
Go to the top of the page
 
tina t
post Sep 13 2017, 11:55 AM
Post#15



Posts: 5,163
Joined: 11-November 10
From: SoCal, USA


QUOTE
No separators other than , and single space

CDex, is each name section in the source data reliably separated by a comma followed by a single space? as

Last, First MI
Last, First, Middle Name
Last, First, Middle Name, Another Middle Name

or is there a comma only after the last name?

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
Cdex
post Sep 15 2017, 07:49 AM
Post#16



Posts: 44
Joined: 29-December 13



Hi Tina - Only after the last name.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2017 - 07:49 PM