Access VBA string functions are built-in routines for finding, extracting, comparing, formatting, and changing text. Common choices include InStr to locate text, Left, Mid, and Right to take part of a string, and Replace, Trim, and UCase to clean or standardize it.
What Access VBA string functions do
VBA treats text as strings, and its string functions let code work with those values without writing character-by-character logic. They are useful in Access forms, reports, import routines, and procedures that prepare or check data. For example, a routine might remove extra spaces from a customer name, find a delimiter in an imported value, or create a consistent version of a code before comparing it.
Many of these functions are part of VBA, so they are familiar to people who have used VBA in other Office applications. Access also has its own functions, such as Nz, which are useful when working with database values. A function available in an Access query expression is not necessarily available in every VBA host, so distinguish Access-specific functions from general VBA functions when reusing code elsewhere.
In VBA, a function call usually takes the form FunctionName(argument, argument). Put string literals in straight double quotation marks, and use a variable or field value when the text is dynamic. The functions below are most useful when their return values, optional arguments, and handling of Null values are understood.
Finding text with InStr and InStrRev
InStr searches for one string inside another and returns the position where the match begins. Positions start at 1; a return value of 0 means the text was not found. For example, InStr(1, "Access VBA", "VBA", vbTextCompare) returns 8. Its arguments are the starting position, the text to search, the text to find, and an optional comparison mode. The starting position and comparison mode can be omitted in some calls, but providing them makes the intended behavior clearer.
InStrRev searches from the end of a string toward the beginning. It is helpful when the final occurrence matters, such as finding the last period in a filename or the last slash in a path. It still reports a position counted from the start of the original string. Do not confuse it with InStr simply called with a larger starting position: the reverse search is designed to locate the last match.
- Use
InStrto test whether a substring appears and to find its first position. - Use
InStrRevwhen you need the last occurrence. - Check for a result of 0 before using the returned position in another operation.
Extracting and measuring parts of a string
Left, Right, and Mid return sections of a string. Left(text, count) takes characters from the beginning, while Right(text, count) takes them from the end. Mid(text, start, count) begins at a one-based position and takes the requested number of characters. The count in Mid is optional; if omitted, the function returns from the start position through the end of the string.
Len returns the length of a string in characters as VBA counts them. For instance, Len("Access") returns 6. These functions are useful for fixed-format values, but fixed positions can be fragile if the source data changes. When a value has a delimiter, locating that delimiter with InStr and then extracting around it is often safer than assuming every value has the same layout.
The related names LeftB, RightB, MidB, and LenB work with byte-oriented lengths rather than ordinary character counts. They are specialized and are rarely the right choice for normal Access text processing. VBA strings use Unicode internally, so byte-oriented operations can be confusing when characters do not map simply to one byte. For typical names, addresses, and labels, use the non-B versions.
Changing and cleaning text
Trim removes spaces at both ends of a string; LTrim removes them from the left, and RTrim removes them from the right. These functions do not remove every kind of whitespace character, such as tabs, just because they remove ordinary spaces. If an import contains tabs or unusual whitespace, identify and handle those characters explicitly rather than assuming Trim removes them all.
LCase and UCase convert text to lower or upper case. Replace substitutes occurrences of a search string with replacement text. Its optional arguments can specify where to start, how many matches to replace, and how to compare. For example, Replace("A-12-A", "A", "X") returns X-12-X. By default, the comparison behavior may depend on the module’s comparison setting, so specify the compare argument when the result must be consistent.
StrReverse reverses the order of characters. String creates a repeated character, and Space creates a string of spaces; these can be useful for padding or simple formatting. For code that should return a String rather than a Variant, VBA also provides dollar-suffixed forms such as Trim$, Left$, and Mid$. Those forms are convenient in string-heavy code, but they do not make a Null input safe.
Splitting, joining, and formatting
Split breaks a string into an array using a delimiter. For example, splitting "red,green,blue" on a comma produces three elements, indexed from 0 by default. That zero-based indexing is a common source of mistakes for developers who expect the first element to be at index 1. An optional limit can restrict the number of elements, and an optional comparison argument controls matching behavior.
Join combines an array of strings into one string, placing a chosen delimiter between the elements. It is useful after editing a list produced by Split. Neither function automatically validates the data: an empty field between delimiters remains an empty element, and joining values does not escape delimiters that appear inside the values. For structured files such as CSV, use a proper parsing approach rather than treating every comma as a field boundary.
Format can create a text representation of a date, number, or other value using a format expression. It is useful for display, but the result is text, not the original date or number. Keep values in their native data types for sorting, calculations, and database comparisons; format them when they need to be shown or exported as text. CStr converts a value to a String, but it can fail for values that cannot be converted, so validate inputs when they may contain unexpected data.
Comparison rules and choosing the right function
Text comparison can be binary, which compares character values, or textual, which follows text comparison rules. In VBA, StrComp compares two strings and returns -1, 0, or 1 when the first is less than, equal to, or greater than the second under the selected comparison mode. It can also return Null when a Null value is involved, so check values before using the result as a Boolean test.
A module-level Option Compare statement can affect comparisons when a function’s optional comparison argument is omitted. Access modules commonly include Option Compare Database, while VBA defaults to binary comparison if no option is specified. To avoid relying on an unseen module setting, pass a comparison constant such as vbBinaryCompare or vbTextCompare where the function supports it. The database’s locale and collation can also matter for text ordering; do not assume every system sorts accented or non-English characters identically.
- Choose
InStrorInStrRevto locate a substring. - Choose
Left,Mid, orRightto extract a known section. - Choose
Replacefor substitutions, andTrimfor leading or trailing ordinary spaces. - Choose
SplitandJoinfor simple delimiter-separated lists. - Choose
StrCompwhen you need an explicit comparison result or comparison mode.
Handling Null values in Access
Access fields can contain Null, meaning the value is missing or unknown; Null is not the same thing as an empty string. String functions generally should not be called on a value that might be Null without first deciding what the missing value means. Depending on the function and how the expression is used, Null may propagate or cause an error. A reliable routine checks for Null before converting or manipulating the value.
Access provides Nz to substitute a value for Null. In VBA, supplying an explicit replacement such as Nz(value, "") makes the intended empty-string behavior clear. Do not replace Null automatically if it represents meaningful missing data: for example, an unknown middle name and a deliberately blank middle name may need to remain distinct. Use IsNull when that distinction matters.
This example shows a small reusable function that returns a trimmed string, using an empty string for Null. It converts the value only after checking it. Before running VBA that changes records or other database content, save a copy of the Access database: changes made by VBA cannot be undone with Ctrl+Z.
Public Function CleanLabel(ByVal value As Variant) As String
Dim textValue As String
' Treat Null as an empty label.
If IsNull(value) Then
CleanLabel = vbNullString
Exit Function
End If
' Convert the non-Null value, then remove outer spaces.
textValue = CStr(value)
CleanLabel = Trim$(textValue)
End Function
Using string functions in VBA versus queries
Access string functions can appear in VBA procedures and in query expressions, but the surrounding syntax differs. In VBA, values are passed as arguments to a function and assigned to variables. In a query, an expression is evaluated for each record, often using a field name. For example, a query expression can use Left([PostalCode], 3) to display the first three characters of a field. A field that is Null still needs deliberate handling, particularly if the result feeds a filter, join, or calculated field.
When choosing between a query expression and VBA, prefer a query for a transformation that belongs in a result set, such as selecting a prefix or calculating a normalized display column. Use VBA when the operation needs branching, repeated steps, interaction with a form, or a reusable procedure. Keep in mind that formatting a value as text in a query can affect later sorting and calculations, and that putting complex logic in many separate queries can make a database harder to maintain.
Common mistakes and troubleshooting
- Confusing character positions: VBA string positions begin at 1, but arrays returned by
Splitbegin at 0. Check each indexing convention before combining the functions. - Assuming a search succeeded:
InStrreturns 0 when there is no match. Test the result before passing it as a starting position toMid. - Ignoring Null: A field may be Null even when most records contain text. Test for Null or supply an intentional substitute before conversion.
- Expecting Trim to remove all whitespace: It handles ordinary spaces at the ends, not every possible invisible character or spaces in the middle.
- Relying on implicit comparison settings: Explicitly specify a comparison mode where supported if case or linguistic comparison affects the result.
- Using text output as data: Functions such as
Formatreturn presentation text. Preserve the original typed value when calculations or date comparisons are still needed.
For errors, inspect the exact input and the function’s argument positions. A negative length supplied to a substring function, an invalid start position, an unexpected Null, or a failed type conversion can all explain a failure. Testing with an empty string, a typical value, a value containing the delimiter, and a Null value is a practical way to expose edge cases.
Frequently Asked Questions
What is the most useful string function in Access VBA?
There is no single best function. InStr is a frequent choice for locating text, while Trim, Replace, and the substring functions solve different common tasks. Choose based on whether you need to find, extract, or change text.
How do I find a word in a string in Access VBA?
Use InStr and check whether its return value is greater than 0. Pass vbTextCompare when a case-insensitive text comparison is wanted, or use a different comparison mode when that better fits the data.
How can I remove spaces from text in Access?
Use Trim or Trim$ to remove ordinary spaces at both ends. Use Replace if you need to remove or change spaces inside the text, and handle tabs or other whitespace characters separately.
What is the difference between Left and Mid?Left always takes characters from the beginning of a string. Mid lets you specify a starting position, so it can extract a section from the middle or from any other position.
Do Access VBA string functions handle Null?
Do not assume that Null behaves like an empty string. Check with IsNull or use Access’s Nz with an explicit replacement, after deciding whether treating missing data as blank is appropriate.
Related
- Enum in Programming: Meaning, Uses, and Examples
- Java Enum Values: Meaning, Methods, and Examples
- What Is ENUM in SQL? How It Works and When to Use It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range.Delete: Remove Cells, Rows, or ColumnsIn Excel VBA, use the Delete method to remove cells, rows, or columns: for example, Range("B2:C5").Delete Shift:=xlShiftUp deletes…
- Excel VBA Open Workbook Dialog: A Practical How-ToTo display a workbook picker in Excel VBA, call Application.GetOpenFilename, check whether the user cancelled, and pass the…
- Excel VBA: Check If a String Contains a Substring in a CellIn Excel VBA, use InStr to check whether one string contains another: it returns a position greater than…
- What Does Split Return in VBA? Array Results ExplainedIn VBA, Split returns a Variant containing a zero-based, one-dimensional array of strings. It creates one array element…
- Excel VBA: Find a Date in a RangeIn Excel VBA, use the Range.Find method to locate an exact date in a range; set its search…
- Excel VBA: Find the Minimum Value in a Range or ArrayTo find a range’s minimum in Excel VBA, use Application.WorksheetFunction.Min, for example, Application.WorksheetFunction.Min(Range("B2:B20")). For a macro you can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.