eliminating middle initial (1 Viewer)

[teiben]

I'm rusty and could use help, I have table where First Name, Last Name and Initial are all in one field. What is the syntax to remove initial? Been playing with Right and Len with no success, I'm sure this has been done before.

 

[BlueIshDan]

within a query or vba?

 

[BlueIshDan]

Place this function in a new or existing MODULE

Public Function SplitReturn(ByVal str As String, _
                            ByVal delimiter As String, _
                            ByVal index As Integer) As String
                                
    SplitReturn = Split(str, delimiter)(index)
                                
End Function

Then use the function in your query as the example bellow describes:

SELECT 
    SplitReturn(name, " ", 0) As [First Name], 
    SplitReturn(name, " ", 2) As [Last Name]
FROM People

 

[jdraw]

This, along with Addresses, is an age old issue. Many people have built careers on trying to solve this. Even the best attempts have some limitation.

Some names just don't fit the "pattern". Not all people have a middle initial. Some names are not single contiguous letters.

What would you expect from a few of these:


William Smith
Antonio G de Silva
Ali M vant Goor
Mary Beth K Allen
Jai Ng

I agree with Dan's question --where do you want/need/intend to do this?
Can you give us some samples of the data?

 

[teiben]

This will be used in a query. Understood this will not work in all cases, ie. some people have 5 or 6 names, but should work 90%+ of the time.

Some examples are
Flintstone Fred M.
Flintstone Wilma Marie
Flintstone Babmab Jr
Flintstone Fred Barney Jr

 

[BlueIshDan]

You need to come up with some validation rules.
limit the input to 2 characters for the initial or something.

One way to test the current state of the initial lengths is to run a query that returns the results of the distinct shortest word length of each name. That way you can see what the variants are.

 

[BlueIshDan]

Here is a function to test:

Public Function ShortestWordLength(ByVal str As String) As Integer
    
    Dim var_split As Variant
    ShortestWordLength = 0
    
    For Each var_split In Split(str, " ")
        If Len(var_split) > ShortestWordLength Then: ShortestWordLength = Len(var_split)
    Next
    
End Function

Here is a query to see the results:

SELECT DISTINCT ShortestWordLength([name]) As [Shortest World Length]
FROM People

 

[teiben]

error in the code 'undefined function Shortestword length in express.
Just need the query to ignore anything in the third, fourth, etc in the field, this will be the case 90% of the time.

So all middle names, Jr, etc. would be returned,,

 

[teiben]

shorted word length return 2

 

[jdraw]

Here is a routine (from a few years back) that may be helpful. I remember doing this in response to a post.

This code includes a function and a test routine, and a file of test names.

'---------------------------------------------------------------------------------------
' Procedure : ParseName
' Author    : Jack
' Created   : 2/23/2010
' Purpose   : To parse a field containing the person's full name and to return
' the first name, or the initial if it exists, or last name depending on the
' value of strWhich.
'
' NOTE: The format of the fullname field is
'       Lastname, Firstname Initial(may not be present)
' eg    a)De Jesus, Cheryl W.
'       b)O'Sullivan, Margaret
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: strname == the person's fullname
'         strWhich = F  First Name
'                  = M  Middle Initial
'                  = L  Last Name
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function ParseName(strName As String, strWhich As String) As String

        
      Dim strUtil As String
      Dim strLastname As String
      Dim strFirstname As String
      Dim strMiddle As String
10       On Error GoTo ParseName_Error

20    strUtil = Trim(strName)
30    strLastname = Left(strUtil, InStr(1, strUtil, ",") - 1)
40    strMiddle = Mid(strUtil, InStrRev(strUtil, " ") + 1)
50        If Len(strMiddle) <> 1 Then
60             strMiddle = vbNullString
70        Else
80            ParseName = strMiddle
90            strUtil = Mid(strUtil, 1, Len(strUtil) - 2)
100       End If
110   strFirstname = LTrim(Mid(strUtil, InStr(1, strUtil, ";") + 1))

120   Select Case strWhich
      Case "F"
130   ParseName = strFirstname
140   Case "L"
150   ParseName = strLastname
160   Case "M"
170   ParseName = strMiddle
180   Case Else
190   ParseName = vbNullString
200   End Select


210      On Error GoTo 0
220      Exit Function

ParseName_Error:

230       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ParseName of Module Module4"
         
End Function

TEST Routine

Sub jnames()
      Dim db As DAO.Database
      Dim rs As DAO.Recordset
10    Set db = CurrentDb
20    Set rs = db.OpenRecordset("TestNames")
30    Do While Not rs.EOF
40    Debug.Print rs!FullName & " -**- " & ParseName(rs!FullName, "F") & ParseName(rs!FullName, "M") & " " & Trim(ParseName(rs!FullName, "L"))
50    rs.MoveNext
60    Loop
End Sub

Test data is in the attached txt file. It is semicolon delimited.

 

[plog]

Is this a one time deal or is this something that needs to be done often? Hopefully, you are structuring your data properly ( a field for each discrete piece of data) and what you need to do now will only be done once and going forward the data will be input and stored correctly.

Is that the case? Will this be an ongoing thing or a one time deal?

 

[BlueIshDan]

If you only need the first two words use my inital suggestion but change the indexes to what you want lol.

 

[teiben]

one time deal

 

[teiben]

tried the Public Function SplitReturn(ByVal str As String, _
ByVal delimiter As String, _
ByVal index As Integer) As String

SplitReturn = Split(str, delimiter)(index)

End Function

I get an error runtime error 9, subscript out of range; the order of the field is last name, first name, middle; the code returns last name and middle initial; I changed the numbers to 1 and 2, which put first name in correct location, but it puts the middle initial in the last name position and it errors when there is no middle initial; tried adding another line to SQL code SplitReturn(name, " ", 3) As [middle Name], that didn't work either...seems close though

 

[BlueIshDan]

That means you have some dirty, inconsistent data.
I can modify the function to handle this, but I advise you to go and try to clean it up.

Try that out.

Public Function SplitReturn(ByVal str As String, _
                            ByVal delimiter As String, _
                            ByVal index As Integer) As String

    Dim split_str() As String: split_str = Split(str, delimiter)
    
    If UBound(split_str) >= index Then: SplitReturn = split_str(index)
                                
End Function

From what I can tell, your indexes will be:
firstname = 1
lastname = 0

 

[teiben]

using SELECT
SplitReturn(patientname, " ", 0) As [Last Name],
SplitReturn(patientname, " ", 1) As [First Name]
FROM Table1

works Great.. even tried it with a person with 5 names1