Access: Replace Function

In Access, the Replace function returns a string in which a sequence of characters has been replaced with another set of characters a specified number of times.

The syntax for the Replace function is:

Replace ( expression, find, replacement, [start, [count, [compare]]] )

The return value of the Replace function is a string, that begins at the position specified by start and concludes at the end of the expression string. It is not a copy of the original string from start to finish.

expression is the string expression containing substring to replace.

find is the substring being searched for in expression.

replacement will replace find in expression.

start is optional. This is the position in expression to begin the search. If this parameter is omitted, 1 is assumed.

count is optional. This is the number of substitutions to perform. If omitted, the default value is 1, which means make all possible substitutions.

compare is optional. The following is a list of valid parameters:

Example

Replace("Green Apple", "Green", "Yellow")      returns "Yellow Apple" 
Replace("Green Apple", "e", "a", 7)                returns "Green Appla" 
Replace("Green Apple", "e", "a", 1, 2)            returns "Graan Apple"

VBA Code

Dim MyResult as String 
MyResult = Replace("Green Apple", "Green", "Red") 

This example uses the replace function to change the apples color. Now the MyResult variable would contain the value “Red Apple”.

SQL query

You can also use the Replace function in a query.

SELECT Replace([ItemDesired],'e','i') AS Expr1
FROM Orders 

Note

You cannot test this function online. It works in Access only.