MS Access VBA String Functions: A Practical Guide

VBA & macros · Updated November 22, 2026

MS Access VBA string functions let you extract, search, compare, clean, and assemble text in procedures and functions. The most useful starting points are Left$, Mid$, Right$, InStr, Replace, Trim$, Split, and Join; choose among them according to whether you are locating text, changing it, or breaking it into parts.

Where Access string functions fit

Access has a VBA programming environment as well as query expressions, and many familiar text functions can be used in both. In VBA, you write expressions inside a procedure or function, typically in the Visual Basic Editor. In a query, you can use expressions such as Left([FullName], 4) in a calculated field, but you do not declare VBA variables there. The same function name can therefore appear in two places even though the surrounding syntax and workflow differ.

Access is a Windows desktop application; there is no Microsoft Access desktop version for macOS. In Access for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. A Mac keyboard shortcut or equivalent Access VBA environment does not apply to the desktop Access application. String functions operate on text values, but the value you receive from a table, form, or query may be Null, which needs separate attention.

Many VBA functions have a dollar-suffixed form, such as Left$ or Trim$. These return a String rather than a Variant, which can make the intended data type clearer in VBA. The non-suffixed forms are also useful, especially when you need to work with Variant values that may contain Null.

Extracting text with Left, Right, and Mid

Use Left for characters at the start of a string, Right for characters at the end, and Mid for a section beginning at a specified position. VBA positions start at 1, not 0. If the requested length is longer than the available text, these functions return the characters that are available; a negative length is invalid.

  • Left$("INV-483", 3) returns INV.
  • Right$("INV-483", 3) returns 483.
  • Mid$("INV-483", 5, 3) returns 483.
  • Mid$("Access VBA", 8) returns the text from position 8 through the end.

The optional length argument to Mid can be left out when you want the remainder of the string. These functions do not identify meaningful boundaries on their own: extracting the first three characters works only if the data consistently puts the desired value in those positions. For names, codes, or labels with variable formats, first locate a delimiter with InStr.

Finding characters and delimiters

InStr searches for one string inside another and returns the position where the match begins. It returns 0 when it finds no match. Its optional first argument sets the starting position; the optional compare argument controls how text is matched. The common choices are vbBinaryCompare for a binary, case-sensitive comparison and vbTextCompare for a text comparison. If you omit the comparison argument, the module’s Option Compare setting controls the default.

For example, if a value contains a dash, find its position before extracting either side. This procedure is suitable for a standard Access VBA module. Save a copy of the database before running a macro: VBA changes cannot be undone with Ctrl+Z.

Public Sub ShowTextBeforeDash()
    Dim sourceText As String
    Dim dashPosition As Long
    Dim prefix As String

    ' Example input; change it to a value from your application.
    sourceText = "INV-483"
    dashPosition = InStr(1, sourceText, "-", vbTextCompare)

    If dashPosition > 0 Then
        prefix = Left$(sourceText, dashPosition - 1)
        Debug.Print prefix
    Else
        Debug.Print "No dash found"
    End If
End Sub

Here, the result printed is INV. The test for a position greater than 0 matters: without it, subtracting 1 from a not-found result could produce an invalid length. InStrRev searches from the end and is handy when a string can contain several separators, such as finding the final backslash in a file path.

Replacing text and changing case

Replace returns a new string with matching occurrences changed; it does not modify the original variable unless you assign the result back. For example, assigning the result of Replace(sourceText, "-", "/") changes each matching dash to a slash in the returned text. Its optional arguments let you set a starting position, limit the number of replacements, and choose a comparison mode. Check the function’s result or assign it explicitly when you need the changed value later.

UCase$ and LCase$ convert letters to uppercase or lowercase. They do not remove spaces or punctuation. Use them when normalizing a value for display or comparison, but avoid changing stored data solely to make a search case-insensitive if the comparison mode can do that directly. Case conversion and text comparison can depend on locale, so test with the languages and characters your database actually stores.

StrConv offers additional conversions, including proper case through vbProperCase. Proper-case conversion is not a dependable way to format every person’s name: it may not preserve a person’s preferred capitalization, and language-specific rules can differ. Treat it as a formatting option for suitable data, not as a universal name-correction rule.

Trimming spaces and cleaning input

Trim$ removes ordinary leading and trailing spaces. LTrim$ removes them only from the left, while RTrim$ removes them only from the right. These functions do not remove spaces between words, and they should not be assumed to remove every whitespace character. Tabs, line breaks, and nonbreaking spaces may need to be handled separately if they occur in imported or pasted data.

A frequent source of confusing results is assuming that a visible gap is an ordinary space. Inspect the input or explicitly replace known unwanted characters. For example, a tab can be represented by vbTab in VBA and a line break by vbCrLf or another appropriate newline constant. Do not strip punctuation or internal spaces indiscriminately: cleaning should reflect what the field is supposed to contain.

For database text fields, distinguish between an empty string and Null. An empty string has zero characters; Null means that no value is present. Trimming an empty string is fine, but a Null value should be tested or converted first. The next section covers this distinction because it affects many string operations, not just trimming.

Splitting and joining text

Split divides a string at a delimiter and returns an array of text pieces. By default, its array is zero-based: the first item is at index 0. Join performs the reverse kind of operation by combining an array’s elements with a chosen separator. These functions are useful for simple, consistently delimited values, such as a list of tags separated by semicolons.

Public Sub ShowSplitAndJoin()
    Dim parts As Variant
    Dim combined As String

    ' Split at each semicolon, then join with a comma and a space.
    parts = Split("red;green;blue", ";")
    combined = Join(parts, ", ")
    Debug.Print parts(0)
    Debug.Print combined
End Sub

This prints red on the first line and red, green, blue on the second. Since Split returns an array, declare the receiving variable as a Variant or use an appropriately dimensioned string array when you have a specific reason to do so. If the delimiter is absent, the result is still an array containing the unsplit text.

A crucial limitation: Split is not a full CSV parser. A comma-separated file may contain quoted fields with commas inside them, escaped quotation marks, or line breaks inside a field. Splitting such a line at every comma can corrupt the data. Use Access’s import tools or a parser that understands the file’s quoting rules when handling real CSV data.

Comparisons, formatting, and Option Compare

StrComp compares two strings and returns a negative number, zero, or a positive number when the first string sorts before, matches, or sorts after the second. Its optional comparison argument lets you request binary or text comparison. This is useful when a procedure needs to test or sort strings according to a specific comparison rule rather than relying on an implicit default.

A module-level statement such as Option Compare Database, Option Compare Text, or Option Compare Binary sets the default comparison behavior for functions that use the module’s setting. Database follows the Access database’s sort order; Text requests text comparison; and Binary uses binary comparison. If no statement is present, VBA’s default is Binary. Put an Option Compare statement at the top of the module, before procedures, and specify a compare argument when a particular operation must be explicit.

Format creates a string representation of a value using a format pattern, such as a date or number pattern. It is useful for display, but the returned text is not the original numeric or date value. Keep dates and numbers in their appropriate data types for calculations and comparisons; format them when you need text for a message, label, or report. For numeric text conversion, CStr follows regional settings, while Str has different formatting behavior, including a leading space for positive numbers. Choose deliberately rather than treating the two as interchangeable.

Handling Null values safely

Access fields and controls can contain Null. That is different from a zero-length string, and many string functions cannot safely process Null as though it were ordinary text. Use IsNull to test a value, or Access’s Nz function to supply a replacement. For text, passing an explicit empty-string replacement makes the intent clear.

Public Function SafeTrim(ByVal value As Variant) As String
    ' Convert Null to empty text, then remove outside spaces.
    If IsNull(value) Then
        SafeTrim = vbNullString
    Else
        SafeTrim = Trim$(CStr(value))
    End If
End Function

This handles Null and ordinary text, but it does not handle every possible Variant subtype. For example, a value containing an Error should be checked separately before converting it with CStr. In Access, Nz(value, "") is a concise alternative when converting Null text to an empty string. Supplying the replacement explicitly avoids ambiguity about the intended result.

Also be careful with IIf as a Null guard. VBA’s IIf evaluates both its true and false expressions, so an unsafe string operation in the branch you expect not to use may still cause an error. A normal If...Then...Else block evaluates only the selected branch and is often the safer choice for validation.

Common problems and choosing the right function

  • Use Left$, Mid$, or Right$ when a position or length is known; use InStr first when the boundary depends on a delimiter.
  • Check for a not-found result of 0 before calculating a substring length from InStr.
  • Remember VBA positions start at 1, while the default Split array starts at index 0.
  • Handle Null separately instead of assuming it is equivalent to an empty string.
  • Use an explicit comparison argument when case sensitivity matters, and do not assume Trim$ removes tabs or every Unicode whitespace character.

When debugging, print intermediate values with Debug.Print in the Immediate window, and inspect lengths with Len or LenB where appropriate. Len counts characters in a string; LenB reports bytes used by the string representation. For typical text processing, Len is the relevant choice. A short test with empty text, a missing delimiter, a Null value, and representative international characters can reveal assumptions before a function is used across many records.

Frequently Asked Questions

What is the difference between Mid and Mid$ in Access VBA?
Both extract a section of text. Mid returns a Variant, while Mid$ returns a String. The dollar form can be clearer when the result is definitely text; it does not change the 1-based position rules.

How do I find text before a dash in Access VBA?
Use InStr to find the dash position, check that the result is greater than 0, then use Left$ with the position minus 1. The example above demonstrates this pattern and handles a missing dash.

Does Trim remove tabs and line breaks?
No. VBA’s Trim, LTrim, and RTrim target ordinary spaces at the ends of text. Handle tabs, line breaks, or other characters explicitly if your source data contains them.

Why does a string function fail on an Access field?
The field may contain Null rather than text. Test it with IsNull or convert it with Nz(field, "") before applying a text operation. Also check that arguments such as substring positions and lengths are valid.

Can I use Access VBA string functions in a query?
Many functions, including Left, Mid, Right, InStr, and Replace, can be used in Access query expressions. Query expressions do not use VBA variable declarations, and Null handling and evaluation context still matter.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

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

Spotted an error in this guide? Let us know.