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)returnsINV.Right$("INV-483", 3)returns483.Mid$("INV-483", 5, 3)returns483.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$, orRight$when a position or length is known; useInStrfirst 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
Splitarray 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
- Can You Play Split Fiction Solo? Co-op Options Explained
- Balancing Equations Year 8: Steps, Examples and Tips
- CAGR in Excel with RRI: Calculate Annual Growth Rate
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Copy and Paste Cells: Examples and TipsIn Excel VBA, copy cells with the Copy method: specify a source range and a destination, such as…
- Excel VBA Subscript Out of Range: Causes and FixesExcel VBA “Run-time error 9: Subscript out of range” means your code asked for an item that does…
- Can You Exit Multiple Loops in VBA? Practical MethodsYes, you can stop multiple nested loops in VBA, but there is no single Exit All statement. Exit…
- VBA Find If: Check Whether a Value Exists in ExcelIn Excel VBA, use the Range.Find method to look for a value, then test whether the returned range…
- Excel VBA Loop Over an Array: Examples and PatternsTo loop over a VBA array in Excel, use For Each when you need to process every item,…
- VBA Sort Key: How Key1 and SortFields WorkIn VBA, a sort key is the cell or range that tells Excel which column’s values to use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.