Replace Function

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

Returns a String in which a specified substring has been replaced with another substring a specified number of times.


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

The Replace function syntax has these arguments:

expressionRequired. String expression containing substring to replace.
findRequired. Substring being searched for.
replaceRequired. Replacement substring.
startOptional. Position within expression where substring search is to begin. If omitted, 1 is assumed.
countOptional. Number of substring substitutions to perform. If omitted, the default value is –1, which means make all possible substitutions.
compareOptional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.


The compare argument can have the following values:

vbUseCompareOption–1Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare0Performs a binary comparison.
vbTextCompare1Performs a textual comparison.
vbDatabaseCompare2Microsoft Office Access 2007 only. Performs a comparison based on information in your database.

Return Values

Replace returns the following values:

IfReplace returns
expression is zero-lengthZero-length string (“”)
expression is NullAn error.
find is zero-lengthCopy of expression.
replace is zero-lengthCopy of expression with all occurences of find removed.
start Len(expression)Zero-length string. String replacement begins at the position indicated by start.
count is 0Copy of expression.


The return value of the Replace function is a string, with substitutions made, 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.


SELECT ProductID, Replace(ProductID,”PRO”,”Product”) AS ReplacedID FROM ProductSales;Returns the “ProductID”, finds “PRO” in “ProductID” and replaces with “Product” and displays the result in column ReplacedID.

See Also

String functions and how to use them

This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.

Leave a Reply

%d bloggers like this: