Access – Using Split() in a Query

I often needed to use the Split() Function in queries and was confronted with the  “Undefined function ‘Split’ in expression”.  Now don’t get me started as to why such a basic function would not be natively accessible in the query editor, but at least there is a very simple solution.

The Workaround

Although the query editor can’t access the Split() function, it can access any of the public user-defined functions, so we need only create a wrapper for the Split() function.

'---------------------------------------------------------------------------------------
' Procedure : String_Split
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Wrapper for the VBA Split() function to make it available for use in
'               query editor.
'             It is Zero-based, so to return the 1st element you must use an index = 0
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput    : String to split up
' lIndex    : Wich element to return
' sDelim    : Delimeter to use to break the string up based upon
'
' Usage:
' ~~~~~~
' String_Split("a,b,c,d,e,f", 2)            -> returns c
' String_Split("514-555-6666", 1, "-")      -> returns 555
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2018-04-22              Initial Public Release
' 2         2020-02-27              Bug Fix
'---------------------------------------------------------------------------------------
Public Function String_Split(sInput As String, _
                      lIndex As Long, _
                      Optional sDelim As String = ",") As String
    On Error GoTo Error_Handler

    String_Split = Split(sInput, sDelim)(lIndex)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    If Err.Number = 9 Then
        String_Split = ""
    Else
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: String_Split" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
    End If
    Resume Error_Handler_Exit
End Function

Instructions

  • Simply copy/paste the above functions into a standard VBA module – either an existing one or into a new one.
  • Compile the code and save everything.
  • Now go back into your query and use the String_Split() function wherever you need.

26 responses on “Access – Using Split() in a Query”

  1. John

    Very nifty! I made something very similar to this but it was VERY specific…this is a much better option – one that is going in my own Code Library.

    Thanks for sharing!

  2. Jose Grande

    Haha! I didn’t know you could do this, and I’ve been Programming in VBA for Access for 3 years!
    I created my own based on yours. It goes:

    Public Function String_Split(sInput As String, lIndex As Long) As String
    String_Split = Split(sInput)(lIndex)
    End Function

  3. LisaGreen

    I believe split() is one of the functions contained in the little used or know VBA String object.

    I’ve had problems before where things like Left$ and Left are not recognised.

    If you replace Split with String.Split I think the builder will except that.

    Lisa

    1. Daniel Pineault Post author

      No, sadly that doesn’t work either and returns a “Undefined function ‘String.Split’ in expressions.” error.

      If you use the expression builder and review the available functions, you’ll see Split simply isn’t available.

      It really does need to be done through a wrapper function.

  4. AJG

    Great solution, but i think i maybe missing something. When I try to run the sample function
    «Expr» String_Split(“514-555-6666”, 1, “-“)

    I get an error
    “Expression you entered contains invalid syntax.
    You may have entered an operand without an operator.”

  6. ChillEDog

    Well done, and thank you.
    >> It is Zero-based, so to return the 1st element you must use an index = 0
    maybe make it 1-based, and then inside the proc add a line
    lIndex=lIndex-1
    so user can call it with ordinal and not have to remember “is it 1-based or 0-based” (head-scratch emoticon)

    1. Daniel Pineault Post author

      I had contemplated that, but then decided to go with the 0-based approach as it mimics the split() function, trying to keep thing standardized.

  7. Marjan Vrban

    Hello,
    this function function is pefect, but i got data where some fields don’t have value, So In querry it returns #Error.
    Data is dimensions in this format 195x65x65 and i’m splitting lenght, width and height in separate fields, some source fields have value others are emtpy. Can it output empty field instead #Error?

    1. Daniel Pineault Post author

      Can you post your SQL Statement?

      Could you be passing Null values to the function which is expecting a string? Perhaps add use of NZ(), for example instead of String_Split([FieldName], 2) you would do String_Split(Nz([FieldName], “”), 2).

  8. Marc Brown

    Great Function, but unfortunately it doesn’t work when linking to Excel. I use Access to query my SQL Database, but use Excel for employees to look at the data more conveniently. I extracted a couple of fields using this split function, but when I do that the query is not available to link into an Excel spreadsheet. If I remove the fields that use this function, then it becomes available to Excel.

  12. Patrick

    Nice function, but one should NEVER use a Msgbox in a function that must be used in a query – unless ready clicking 250,000 times on the “OK” button.

    1. Daniel Pineault Post author

      While this is true, what I post here are snippets of code that can be used in a multitude of ways. I normally always include my default error handler. It is up to the user to customize it further to suit their use case.

  13. Holly

    I have been happily using this function, thank you. I just started to get an error with this function that I am hoping you can help me with. When I use the function, I can no longer type anything in to the criteria field. Whenever I do I get the error, “Data type mismatch in the criteria expression.” I am using MS Access 2019. Any ideas what might be happening?

    1. Daniel Pineault Post author

      Data Type Mismatch normally indicates you are supplying the wrong type of input variables as arguments.

      So say the function is expecting a string and you try passing an array, etc…, but without seeing your code it is impossible to diagnose.

  14. Robert

    Thanks Daniel for this function. How would I go about looping it to pull out all data?

    Example
    LINES:, 5555552154, 5555552155, 5555552156, 5555552157, 5555552158, 5555552159, 5555552160, 5551552161, 5551552162, 5551552163, 4555155164

    Output
    5555552154
    5555552155
    5555552156
    5555552157
    5555552158
    5555552159
    5555552160
    5551552161
    5551552162
    5551552163
    4555155164

    1. Daniel Pineault Post author

      The easiest approach could look a little like

      Const sInput = "5555552154, 5555552155, 5555552156, 5555552157, 5555552158, 5555552159, 5555552160, 5551552161, 5551552162, 5551552163, 4555155164"
aInput = Split(sInput, ", ")
For i = 0 To UBound(aInput)
    Debug.Print aInput(i)
Next i

      If you want to stick with using this function, then you could do something like

      Sub TestMe2(ByVal sInput As String, iNoElements As Long)
Dim i As Long

For i = 0 To (iNoElements - 1)
    Debug.Print String_Split(sInput, i, ", ")
Next i
End Sub

      Or

      Sub TestMe3(ByVal sInput As String)
Dim iNoElements As Long
Dim i As Long

iNoElements = UBound(Split(sInput, ", "))
For i = 0 To iNoElements
    Debug.Print String_Split(sInput, i, ", ")
Next i
End Sub
  15. Overdrive80

    Good solution, maybe I would wrapper Split function with Trim:

    “` String_Split = Split(sInput, sDelim)(lIndex) “`

    2. Daniel Pineault Post author

      Sometimes people want the string unedited, even with extra spaces.

      And you can always add the Trim to your call

      sMyValue = Trim(String_Split("a,b,c,d,e,f", 2))

      At the same time, as you point out, it isn’t hard to add it to the function if you prefer it that way.

      Whatever makes you happy!