Splitting a field in Access


  • I am using access 2013.

    I have a name field with the Following

    Record1: FirstName LastName

    Record2: FirstName MiddleInitial LastName

    Record3: FirstName MiddleName LastName

    Each Name is separated by a space.

    I tried:

    SPLIT1: IIf(InStr(1,[Name]," "),Left([Name],InStr(1,[Name]," ")-1),[Name]) 

    And it give the first name by itself.

    I tried:

    SPLIT2: IIf(InStr(1,[Name]," "),Mid([Name],InStr([Name]," ")+1,InStr(Mid([Name],InStr([Name]," ")))-2),[Name])

    for Middle name but in the field it shows #Func!

    I have no idea how to get the lastname

    Friday, April 19, 2013 11:22 PM


All replies

  • Try something like:

    SELECT Trim(Left([Name],InStr([Name]," ")-1)) AS FirstName, Trim(Mid([Name],Len([FirstName])+1,Len(Mid([Name],Len([FirstName])+1))-Len([LastName]))) AS MiddleName, Trim(Right([Name],Len([Name])-InStrRev([Name]," "))) AS LastName
    FROM YourTableName;

    Also, be aware that you shouldn't ever name a field Name, it is a reserved word and can lead to errors, see:

    Daniel Pineault, 2010-2012 Microsoft MVP
    MS Access Tips and Code Samples:

    Saturday, April 20, 2013 12:10 AM
  • That kind of thing gets very messy just using expressions.  Split returns a variant with an array for the names, but SQL can not understand the syntax needed to retrieve the individual array elements.

    FirstName: IIf(Instr(Name, " ") > 0, Left(Name, Instr(Name, " ") - 1), "")

    MiddleName: IIf(InStr(Name, " ") > 0 And InStr(InStr(Name, " ") + 1, Name, " ") > 0, Mid(Name, InStr(Name, " ") + 1, InStr(Mid(Name, InStr(Name, " ") + 1), " ") - 1), "")

    LastName: Mid(Name, InStr(InStr(Name, " ") + 1, " "))

    It would be a whole lot more comprehensible to create a Public VBA Function to do that:

    Public Function GetFirstName(Nm As String) As String
    Dim av As Variant
       av = Split(Nm, " ")
       GetFirstName = av(0)
    End Function

    Public Function GetMiddleName(Nm As String) As String
    Dim av As Variant
       av = Split(Nm, " ")
       GetMiddleName = av(1)
    End Function

    Public Function GetLastName(Nm As String) As String
    Dim av As Variant
    Dim k as Integer
       av = Split(Nm, " ")
       For k = 2 to UBound(av)
          GetLastName = GetLastName & av(k)
       Next k
    End Function

    Saturday, April 20, 2013 12:35 AM
  • Like those but what do you do to compensate for Four + names some people have and Prefixes and Suffixes.






    Chris Ward

    Saturday, April 20, 2013 2:02 AM
  • Valid point.  Also let us not forget compound names (whether it be first or last names):

    Kelly Ann
    Lee Ann
    Mary Jane
    and so on...

    Daniel Pineault, 2010-2012 Microsoft MVP
    MS Access Tips and Code Samples:

    Saturday, April 20, 2013 2:13 AM
  • I would do it in several passes adding a field [DONE] to indicate record is done.

    1- Criteria Len([Name]) = Len(Replace([Name]," ","")) --- No spaces therefore assume last name only. Update LName field with [Name] and [DONE] with 1.

    2- Criteria Len([Name]) = Len(Replace([Name]," ",""))+1 --- One space therefore assume first and last name only.  Update FName field with Left([Name], InStr([Name]," ")-1).  Update LName field with Mid([Name], InStr([Name]," ")+1) and [DONE] with 2.

    Assume those with 2 spaces have middle name of initial.  Review those DONE with 2 spaces to verify middle name only.


    Saturday, April 20, 2013 4:36 AM
  • Ok well I go the query to work using:

    LN: Right$([NAME],Len([NAME])-InStrRev([NAME]," "))

    FN: Left$([NAME],InStr([NAME]," ")-1)

    yes I realize Name is a reserve word, but it is what the customer sends to us, so it is what I have to use. Anyway running he above works in access. I don't need the middle with what I need to do.

    But when I call the query in VB using the code below I get the following error. 

    An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

    Additional information: Undefined function 'InStrRev' in expression.

    Again the query works in access without a problem, so why does it fail running query through VB?

    Dim DOLOIM As String = "[LIC_FEE], [MC_FEE], [ID_FEE], [ENH_FEE], [REISS_FEE], [PROB_FEE], [TOT_FEE], [NAME], [ADDR1], [CITY], [ST], [ZIP], [ZIP4], [COUNTRY], [AUTH_CD], [EXP_DAT], [OCR1], [Field18], [OCR2], [Field20], [Field21]"
            Dim sConStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\S\"

            Dim Access228Conn As New System.Data.OleDb.OleDbConnection(sConStr + "DOLO999" + _

            Dim Access228 As New System.Data.OleDb.OleDbCommand( _
                "SELECT " + DOLOIM + " INTO [" + "DOLO" + "] FROM [Text;DATABASE=D:\S\" + "DOLO999" + "\;HDR=No].[" + "DOLO" + ".TXT]", Access228Conn)


            'Update Foreign

            Dim cmd2 As OleDbCommand = New OleDbCommand()
            Dim conn2 As OleDbConnection
            conn2 = New OleDbConnection(sConStr + "DOLO999" + "\DB1.mdb" + vbCrLf)
            cmd2.Connection = conn2
            cmd2.CommandType = CommandType.StoredProcedure
            cmd2.CommandText = "qName"

    Monday, April 22, 2013 3:09 PM
  • Hi Broggy,

    Please check these links to see if they help:

    How to automate Microsoft Access by using Visual C#

    How to Run Access Query from C#?

    Good day.

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 01, 2013 1:05 PM
  • Splitting names is VERY difficult, because of what KCDW mentioned.  If those names are all in a simple format like: FName MName LName

    This may work for you:

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Broggy69 Friday, August 15, 2014 5:23 PM
    Friday, May 10, 2013 8:43 PM