Splitting data entry from one field into multiple fields

[roxannep]

I need to split out some data in one field and have it pasted into separate fields.

Example:

Fields: NAME, FIRSTNAME, LASTNAME

NAME: Bob Jones

needs to split out:

FIRSTNAME: Bob
LASTNAME: Jones

and empty the NAME field.

In some cases, middle initials are included so how to handle those would also be helpful (they can either be dropped or included in the FIRSTNAME field).

I'm pretty much a novice at programming, so if there is a way to handle this in an append query or something, that would be most helpful. But if it needs to be coded, please be specific.

 

[FancyPrairie]

Try this function:

'+********************************************************************************************
'*
'*  Function:   MMC_UnBuildFullName
'*
'*  Author:     FancyPrairie
'*
'*  Date:       August, 1997
'*
'*  Purpose:    This function separates the Last Name, Pedigree, First Name, MiddleName and
'*              returns the result.
'*
'*              For example:  "Smith Jr., Tom R" would be returned as "Tom R Smith Jr."
'*
'-********************************************************************************************

Option Compare Database
Option Explicit

Public Function MMC_UnBuildFullName(ByVal strFullName As String, _
                                    varFirstName As Variant, _
                                    varMiddleName As Variant, _
                                    varstrLastName As Variant, _
                           Optional varPed As Variant)
    
'********************************
'*  Declaration Specifications  *
'********************************

    Dim varName As Variant          'Remaining portion of name
    Dim varPedigree As Variant
    
    Dim i As Integer                'Working variable
    Dim k As Integer                'Working variable
    
'****************
'*  Initialize  *
'****************

    varName = Null
    varFirstName = Null
    varMiddleName = Null
    varstrLastName = Null
    varPedigree = Null
    
    strFullName = Trim(strFullName)

'********************************************************************************************
'*  Determine Last Name ("Smith, William" or "Smith Jr., William" or "Smith" or "Smith,")  *
'********************************************************************************************

    i = InStr(strFullName, ",")
    
    If (i <> 0) Then                                            'IFT, "Smith," or "Smith Jr.,"
        
        k = InStr(strFullName, " ")
        
        If ((k > 0) And (k < i)) Then                           'IFT, "Smith Jr.," (pedigree included)
            varstrLastName = Mid(strFullName, 1, k - 1)
            varPedigree = Trim(Mid(strFullName, k + 1, i - (k + 1)))
        Else                                                    'IFT, "Smith," (no pedigree)
            varstrLastName = Mid(strFullName, 1, i - 1)
        End If
        
        varName = Trim(Mid(strFullName, i + 1))                 'Save everything to right of comma
    
    Else                                                        'IFT, no comma in name
    
        i = InStr(strFullName, " ")                             'Check to see if something after last name
        
        If (i <> 0) Then                                        'IFT, first name must exist
        
            varstrLastName = Mid(strFullName, 1, i - 1)
            varName = Trim(Mid(strFullName, i + 1))
        Else                                                    'IFT, last name only name entered
            varstrLastName = strFullName
            varName = Null
        End If
        
    End If
    
'**************************
'*  Determine First Name  *
'**************************

    If (Not IsNull(varName)) Then                           'IFT, First Name exists
    
        i = InStr(varName, " ")                             'Look for Middle Name
        
        If (i = 0) Then                                     'IFT, "William"
            varFirstName = varName
            varName = Null
        Else                                                'IFT, "William B"
            varFirstName = Mid(varName, 1, i - 1)
            varName = Trim(Mid(varName, i + 1))
        End If
        
    End If
    
'***************************
'*  Determine Middle Name  *
'***************************

    If (Not IsNull(varName)) Then                           'IFT, Middle Name exists
    
        varMiddleName = varName
    
    End If
        
    If (Not IsMissing(varPed)) Then varPed = varPedigree
    
End Function

 

[roxannep]

I'm not sure I'm seeing how this will then deposit the split name into the appropriate fields. I see how the code determines the pieces, but I need each piece then pasted into the appropriate fields.

Again, I'm a novice, so maybe it's not popping out to me where the "John" part is then automatically pasted into the empty "FirstName" field in my "Contacts" table and where "Smith" would be pasted into the "LastName" field of that same record.

Thank you for any further assistance.