Microsoft Access Split Function in Queries: How It Works

Formulas & functions · Updated November 22, 2026

In Microsoft Access, Split is a VBA function that breaks a string into an array using a delimiter, such as a comma or space. Because an Access query needs a single value in each calculated column and Split returns an array, the dependable approach is to call a small public VBA wrapper from the query to return one selected piece.

What the Split function does

Split takes text and a delimiter, then separates the text wherever that delimiter occurs. For example, splitting "red,green,blue" on a comma produces three elements: "red", "green", and "blue". In VBA, the elements are numbered starting at zero, so the first element has index 0.

The function’s general VBA syntax is Split(expression, [delimiter, [limit, [compare]]]). The expression is the text to separate. The delimiter is the character or sequence between pieces; if omitted, VBA uses a space. The optional limit sets the maximum number of pieces, and compare determines how delimiter matching is performed. In a database query, it is usually clearer to supply the delimiter explicitly rather than rely on the default.

A key distinction is that Split is a VBA function, not a native Access SQL clause such as WHERE or GROUP BY. Access can evaluate functions in query expressions, including public functions in a standard VBA module, but a query output field is scalar: it displays one value per record. An array returned by Split is not itself a useful field value. A wrapper bridges that difference by returning one requested element.

Why use a wrapper in a query

In ordinary VBA code, you can assign the result of Split to an array and refer to an element by its zero-based index. Query expressions are not as convenient for working with an array result. Rather than depend on array-indexing syntax inside a query expression, create a function that accepts the source text, delimiter, and desired part number, then returns just that part.

The function below uses a one-based part number to make query expressions easier to read: 1 means the first piece, 2 the second, and so on. It returns Null when the source is Null, the requested number is less than 1, or the requested piece does not exist. If the delimiter is an empty string, it treats the whole input as the first piece.

Add the VBA function to Access

In Access for Microsoft 365 on Windows, open the database and press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, then paste the function into the standard module. Save the module with a descriptive name, such as modTextFunctions. Do not give the module the same name as the function. On a Mac, the exact editor access and shortcut can vary with the Office setup; use Access’s Visual Basic Editor command if the Windows shortcut does not apply.

Public Function AccessSplitPart(ByVal SourceText As Variant, _
                                ByVal Separator As String, _
                                ByVal PartNumber As Long) As Variant
    Dim items As Variant

    ' Return Null for missing input or an invalid one-based part number.
    If IsNull(SourceText) Or PartNumber < 1 Then
        AccessSplitPart = Null
        Exit Function
    End If

    ' An empty separator means the whole input is one part.
    If Len(Separator) = 0 Then
        If PartNumber = 1 Then AccessSplitPart = CStr(SourceText)
        Exit Function
    End If

    ' Split into pieces, then convert the requested number to a zero-based index.
    items = Split(CStr(SourceText), Separator, -1, vbBinaryCompare)
    If PartNumber - 1 <= UBound(items) Then
        AccessSplitPart = items(PartNumber - 1)
    Else
        AccessSplitPart = Null
    End If
End Function

Save a copy of the database before adding or running VBA code. VBA changes and macros cannot be undone with Ctrl+Z. If Access blocks the function because the database is not trusted, use a trusted location or follow your organization’s security policy; do not enable content from an unknown database just to make a query work.

Call the function from a query

Create or open a query in Design View. Add the table containing the text, then enter an expression in an empty Field cell. For a field named FullName, this expression returns the first space-separated piece:

FirstPart: AccessSplitPart([FullName]," ",1)

To return the second piece, use SecondPart: AccessSplitPart([FullName]," ",2). You can also switch to SQL View and include the expressions in a SELECT list. For instance, with a table named Contacts, the query can select the original field alongside the calculated values: SELECT FullName, AccessSplitPart([FullName]," ",1) AS FirstPart, AccessSplitPart([FullName]," ",2) AS SecondPart FROM Contacts;

Suppose FullName contains "Ada Lovelace". The first expression returns Ada, and the second returns Lovelace. For "Ada Byron Lovelace", the second expression returns Byron; it does not return everything after the first space. The function asks for one delimiter-separated piece at a time, which is important when deciding whether it matches the meaning of your data.

Use a delimiter that matches the data

The delimiter must match the actual separator in the field. If a field stores values as "North, East, West", a comma-only delimiter leaves a leading space in the second and third pieces. You can either include the space in the delimiter, as in AccessSplitPart([Regions],", ",2), or clean the returned value with an appropriate trimming function. Including the space only works if the data consistently uses comma followed by one space.

Common delimiters include:

  • A space for simple two-part labels such as "Ada Lovelace".
  • A comma for comma-separated codes such as "A12,B34,C56".
  • A slash for values such as "2026/11/03", if the goal is to extract a component rather than handle the value as a date.
  • A multi-character separator, such as " - ", when the source consistently uses that exact sequence.

Do not assume a space always separates a person’s first and last name. Names can contain middle names, multiple family-name components, prefixes, or suffixes. In those cases, returning the second token does not reliably identify a last name. Likewise, delimiters that appear as part of a value can produce unexpected pieces.

Missing pieces, extra delimiters, and data quality

The wrapper returns Null if a record has fewer pieces than requested. That is often preferable to an error, but it means later calculations or exports may show a blank for that record. If you need a different fallback, such as an empty string or the original text, change the function’s out-of-range behavior deliberately and test it against representative records.

Repeated delimiters create empty pieces. For example, splitting "A,,C" on a comma gives a middle, empty value; the third piece is still C. A trailing delimiter can also leave an empty final piece. These results are not necessarily errors: they reflect the source string. Inspect data with consecutive or leading delimiters before using the extracted pieces in a report or update.

The example uses vbBinaryCompare, which compares delimiter text using binary comparison. For punctuation such as a comma, this distinction normally does not matter. It can matter for letter delimiters, where case-sensitive and case-insensitive matching may differ. You can change the comparison option if your delimiter is alphabetic and the required matching behavior calls for it.

Common problems and troubleshooting

If Access shows #Name? in the query, it may not be able to resolve the function name. Check that the function is declared Public, is in a standard module rather than a form or report module, and that the query uses the exact function name. Also check that the VBA project compiles and that the database’s security settings permit its code to run.

If the query opens a parameter prompt, Access may be treating a misspelled field or function name as a parameter. Verify the field name in the table and confirm that the query expression uses the correct brackets and commas. If the function returns blanks, check whether the field contains Null, whether the delimiter matches exactly, and whether the requested part number exists for those rows.

For a large query, a VBA function called for every row can add processing overhead compared with simple built-in expressions. The practical impact depends on the database and workload. Test the query on a realistic set of records, and avoid repeatedly calculating the same extraction in multiple expressions when you can calculate it once in a saved query and reuse that result.

Alternatives to Split in an Access query

For a simple, fixed extraction, built-in string functions may be enough. For example, InStr can locate the first space and Left can return the text before it. A safe first-token expression is FirstPart: Left([FullName],IIf(InStr([FullName]," ")=0,Len([FullName]),InStr([FullName]," ")-1)). The inner IIf supplies a valid length whether or not a space is present. Access evaluates both result branches of IIf, so simply placing a potentially invalid Left call in one branch can still cause trouble.

For repeated use, complicated parsing, or many variable-length pieces, consider whether the source data should be stored in separate fields or in related rows instead. A comma-separated list in one field is convenient to view, but makes filtering, validation, and updates harder. If the values represent multiple related items, a separate related table is usually a more structured design than extracting tokens in every query. The right choice depends on whether the text is a fixed-format import, a display label, or data that users need to search and maintain.

Frequently Asked Questions

Can I use Split directly in an Access query?
Access queries need scalar values in calculated fields, while Split returns an array. Use a public VBA wrapper such as the one above to return a single selected piece, rather than relying on the query to display or index the array.

Does Split count parts from zero or one?
The array produced by VBA’s Split is zero-based: the first element is index 0. The sample wrapper intentionally accepts one-based part numbers, so use 1 for the first piece in a query.

What happens if the requested part is missing?
The sample function returns Null. For example, asking for part 3 of "Ada Lovelace" returns Null, because only two pieces are present.

Can Split separate text using a comma and a space?
Yes. Pass the exact two-character delimiter ", " if the data consistently contains a comma followed by a space. If spacing varies, split on the comma and handle surrounding spaces separately.

Why does my query show #Name?
Confirm that the wrapper is in a standard module, is declared Public, and has the same spelling in both the module and query. Also check for compile errors, misspelled field names, and database security settings that prevent VBA from running.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.