In Microsoft Access VBA, Split divides a string at each occurrence of a delimiter and returns the pieces in a zero-based array. For example, Split("red,green,blue", ",") returns three elements: red, green, and blue.
That makes Split useful when a field or variable contains several simple values joined by a consistent separator. The key is to treat its result as an array, check for empty or Null input where needed, and avoid using it as a shortcut for parsing complex formats such as quoted CSV files.
What Split Does in Access VBA
Split is a Visual Basic for Applications function available in Access. It searches a source string for a specified delimiter, then separates the text wherever that delimiter appears. The delimiter is removed from the returned pieces; it is not included in the array.
For instance, splitting "North|South|West" on the pipe character produces three strings. The function does not know that these are locations or tags. It simply performs a text operation, leaving your code to decide what each piece means and what to do with it.
The result is a zero-based, one-dimensional array held in a Variant. That means the first element is normally at index 0, not index 1. A common source of errors is assuming that Access arrays always begin at 1. With a Split result, use LBound and UBound when iterating rather than hard-coding the first index.
Syntax and Optional Arguments
The function has this syntax:
Split(expression, [delimiter], [limit], [compare])
expressionis the string to divide.delimiteris the separator. If omitted, the delimiter is a space character.limitsets the maximum number of substrings. If omitted, all possible substrings are returned.comparecontrols how the delimiter is compared. If omitted, the comparison setting comes from the module’sOption Comparestatement; if there is no such statement, binary comparison is used.
For everyday parsing, explicitly supply the delimiter so the code is easy to understand. A comma, semicolon, tab, or pipe can be used, and the delimiter can be more than one character. For example, splitting on "::" looks for that complete two-character sequence, not for either colon individually.
The limit argument is useful when only the first separator matters. With the source text "A:B:C", a limit of 2 returns "A" and "B:C". A limit of 1 returns the source as one piece. The default limit of -1 means return all pieces.
A Basic Access VBA Example
This procedure splits a comma-delimited string and prints each item in the Immediate window. In the VBA editor, open it with Alt+F11, place the code in a standard module, and run the procedure. Open the Immediate window with Ctrl+G to see the output.
Sub ShowSplitItems()
Dim parts As Variant
Dim i As Long
' Split the sample text wherever a comma appears.
parts = Split("red,green,blue", ",")
For i = LBound(parts) To UBound(parts)
Debug.Print i, parts(i)
Next i
End Sub
The output has indexes 0, 1, and 2, paired with the three color names. A Variant is a convenient variable type for the result because Split returns an array inside a Variant. The loop uses the array’s actual lower and upper bounds, which is more robust than assuming a particular number of items.
Before running any procedure that changes records or other database content, save a copy of the Access database. VBA actions cannot be reversed with Ctrl+Z. The example above only prints text, but the same function is often used in procedures that update tables or controls, where a backup is sensible.
Handling Delimiters, Spaces, and Empty Pieces
Split preserves empty pieces created by adjacent delimiters or a delimiter at the beginning or end of the source. For example, splitting "one,,three," on a comma produces four elements: "one", an empty string, "three", and another empty string. This behavior can be important when empty positions represent missing values, but it can also surprise code that expects every element to contain text.
Split does not trim whitespace around each piece. Splitting "apples, oranges" on a comma returns "apples" and " oranges"; the second item begins with a space. If spaces are not meaningful in your data, apply Trim to each element after splitting. Do not trim automatically if leading or trailing spaces are part of the value.
An empty source string is a special case: Split returns an empty array. Code that immediately calls UBound can fail for that result, so test the input before looping. A delimiter that does not occur in a nonempty source produces a one-element array containing the whole source. If the delimiter is a zero-length string, Split also returns the complete source as a single element rather than separating it character by character.
Working Safely with Null Values
Access fields and controls can contain Null, which is different from a zero-length string. Split expects a string expression, so do not pass a possibly Null value without handling it. In Access, Nz can convert Null to a chosen value, but explicit handling can make the intended behavior clearer, especially when an empty input should be skipped.
This example illustrates a guarded approach using a value already stored in a variable:
Sub SplitNonEmptyText()
Dim source As String
Dim parts As Variant
Dim i As Long
' Replace this assignment with text from your application.
source = "alpha; beta; gamma"
If Len(source) = 0 Then Exit Sub
parts = Split(source, ";")
For i = LBound(parts) To UBound(parts)
Debug.Print Trim(parts(i))
Next i
End Sub
If you read from a form control, check IsNull before assigning its value to a String variable, or convert Null deliberately. For example, in form code you can assign Nz(Me!txtItems.Value, "") to a String variable, then decide whether an empty string should mean “no items.” The correct choice depends on the application: treating missing data as empty may be convenient, but it can also hide the difference between unknown and intentionally blank values.
Choosing the Comparison Mode
The optional compare argument matters when the delimiter contains letters and the source may use different letter case. With a binary comparison, uppercase and lowercase characters are treated as different. A text comparison ignores that case difference for the delimiter search. For example, if the delimiter is "END", a text comparison can also match "end".
You can pass VBA constants such as vbBinaryCompare or vbTextCompare. If you omit the argument, a module’s Option Compare setting can affect the default. Access modules commonly use Option Compare Database, which follows the database’s comparison rules. For predictable parsing, specify the comparison mode when case sensitivity could change the result, and choose it based on the exact data format rather than assuming all text comparisons behave alike.
Most delimiters are punctuation marks, such as commas or pipes, so case comparison has no practical effect in those cases. It becomes relevant for word-like separators or markers. If the delimiter should match exactly, use binary comparison; if case differences are intentionally irrelevant, use text comparison.
When Split Is Not a CSV Parser
A frequent misconception is that splitting a comma-separated line always correctly reads CSV data. It does not. CSV can use quotation marks to enclose a field that itself contains commas, and it has rules for escaped quotation marks. A basic Split call does not interpret those rules. For example, a line like "Smith, Jane",42 would be divided at the comma inside the quoted name as well as at the separator between fields.
Split is appropriate when you control the format and know that the delimiter cannot appear inside a value, or when the data is intentionally a simple list. If you are importing a real CSV file, use Access’s text import features or DoCmd.TransferText with an appropriate saved import specification. Those options are designed for delimited files and are safer than building a CSV parser by simply splitting each line.
Also remember that Split returns an array in VBA; it does not turn one table field into multiple query rows automatically. If the goal is to normalize a list stored in a field, code must loop over the pieces and write them to related records, or the data model should be adjusted so each value is stored as its own row. Storing multiple items in one field can make searching, validation, and reporting harder.
Split Compared with Related VBA Tools
Use Split when the task is to divide one string into pieces. Use Join for the reverse operation: it combines array elements into one string with a chosen delimiter. Together, they can support workflows that read a simple delimited list, process its elements, and build a new list.
For locating a single delimiter without creating an array, InStr can find its position, and Left, Mid, or Right can extract text around a known position. This approach is useful when the format has a fixed structure or only one separator. For replacing text rather than separating it, use Replace. These functions solve related but different problems; Split is usually the clearest choice when you need to process every delimited item.
Frequently Asked Questions
Does Access VBA Split start at index 0 or 1?
Its returned array is zero-based, so the first element is normally at index 0. Use LBound and UBound in a loop instead of assuming the bounds.
How do I split a string using a pipe or semicolon?
Pass the character as the delimiter argument, such as Split(source, "|") or Split(source, ";"). In VBA, the pipe character needs no special escaping inside a quoted string.
Can Split use more than one character as a delimiter?
Yes. The delimiter argument can be a string such as "::". Split separates at occurrences of that complete string.
Why does Split leave spaces in my results?
Split removes the delimiter, not surrounding whitespace. Apply Trim to each returned element if those spaces should not be part of the values.
Can I use Split directly in an Access query?
Split is a VBA function that returns an array, not a query operation that expands one field into multiple records. A query cannot use its array result as a set of rows; use VBA to process it or redesign the data into separate records.
What happens if the source is Null or empty?
Handle Null before calling Split. An empty string produces an empty array, so check for empty input before using array bounds or looping through the result.
Related
- T Balance in Accounting: Meaning, Examples, and Uses
- Is Split Fiction Cross-Platform? Crossplay Explained
- Grade 9 Balancing Chemical Equations, Step by Step
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Next Without For Error: Causes and FixesThe Excel VBA compile error “Next without For” means VBA has reached a Next statement without finding a…
- VBA Check If Worksheet Exists: Code and ExamplesTo check whether a worksheet exists in VBA, try to set a Worksheet variable from the target workbook’s…
- Excel VBA Dynamic Worksheet Names: How They WorkIn Excel VBA, make a worksheet name dynamic by storing the desired tab name in a string variable…
- Excel VBA Dim As Workbook: Declare and Use ItIn Excel VBA, declare a workbook variable with Dim wb As Workbook, then assign it to an open…
- VBA Find and Replace: Methods, Examples, and Tips in ExcelIn Excel VBA, use Range.Find to locate matching cells and Range.Replace to change matching content. For example, Worksheets("Data").Range("A1:D100").Replace…
- Excel VBA Syntax Error: Causes, Examples, and FixesAn Excel VBA syntax error means the Visual Basic for Applications editor cannot parse part of your code…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.