MS Access: Replace Function
This MSAccess tutorial explains how to use the Access Replace function with syntax and examples.
Description
The Microsoft Access Replace function replaces a sequence of characters in a string with another set of characters (a number of times).
Syntax
The syntax for the Replace function in MS Access is:
Replace ( string1, find, replacement, [start, [count, [compare]]] )
Parameters or Arguments
- string1
- The string to replace a sequence of characters with another set of characters.
- find
- The string that will be searched for in string1.
- replacement
- The string to replace find in string1.
- start
- Optional. This is the position in string1 to begin the search. If this parameter is omitted, the Replace function will begin the search at position 1.
- count
- Optional. This is the number of occurrences to replace. If this parameter is omitted, the Replace function will replace all occurrences of find with replacement.
- compare
Optional. This can be one of the following values:
Parameter Value Description vbUseCompareOption Compares based on the Option Compare statement vbBinaryCompare Binary comparison vbTextCompare Textual comparison vbDatabaseCompare Performs a comparison based on information in your database
Applies To
The Replace function can be used in the following versions of Microsoft Access:
- Access 2019, Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000
Example
Let's look at how to use the Replace function in MS Access:
Replace("alphabet", "bet", "hydro") Result: "alphahydro" Replace ("alphabet", "a", "e") Result: "elphebet" Replace("alphabet", "a", "e", 2) Result: "lphebet" Replace("alphabet", "a", "e", 1, 1) Result: "elphabet"
Example in VBA Code
The Replace function can be used in VBA code in Microsoft Access.
For example:
Dim LResult As String LResult = Replace ("alphabet", "a", "e")
In this example, the variable called LResult would now contain the value "elphebet".
Example in SQL/Queries
You can also use the Replace function in a query in Microsoft Access.
For example:
In this query, we have used the Replace function as follows:
Expr1: Replace([name],"a","e")
This query will replace all "a" characters in the name field with "e" characters. The results will be displayed in a column called Expr1. You can replace Expr1 with a column name that is more meaningful.
For example:
ModifiedName: Replace([name],"a","e")
The results would now be displayed in a column called ModifiedName.
Advertisements