John,
Pardon me. But I seem to recall that Replace was not added until Access 2000.
Before that you needed to use a custom VBA function. Here is one from my
archives.
'Courtesy of John Viescas
Public Function Replace(strIn As Variant, strFind As String, _
strReplace As String, Optional intStart As Integer = 1, _
Optional intCount As Integer = -1, _
Optional intCompare As Integer = 0) As String
'-----------------------------------------------------------
' Inputs: String to search and replace,
' search string, replacement string,
' optional starting position (default = 1),
' optional replacement limit (default = -1 .. ALL)
' optional string compare value (default = 0 .. vbBinaryCompare)
' Outputs: Replaced string
' Created By: JLV 09/05/01
' Last Revised: JLV 09/05/01
' ** Duplicates the functionality of the VB 6 REPLACE function.
'-----------------------------------------------------------
Dim strWork As String, intS As Integer, intCnt As Integer
Dim intI As Integer, intLenF As Integer, intLenR As Integer
If (intCompare < 0) Or (intCompare > 2) Then
Err.Raise 5
Exit Function
End If
If VarType(strIn) <> vbString Then
Err.Raise 5
Exit Function
End If
strWork = strIn
intS = intStart
intCnt = intCount
intLenF = Len(strFind)
intLenR = Len(strReplace)
' If find string zero length or count is zero, then nothing to replace
If (intLenF = 0) Or (intCnt = 0) Then
Replace = strIn
Exit Function
End If
' If start beyond length of string, return empty string
If intS > Len(strWork) Then
Replace = ""
Exit Function
End If
' Got some work to do -- find strings to replace
Do
intI = InStr(intS, strWork, strFind, intCompare)
If intI = 0 Then Exit Do
' Insert the replace string
strWork = Left(strWork, intI - 1) & _
strReplace & Mid(strWork, intI + intLenF)
'Bump start to end of the replace string
intS = intS + intI + intLenR - 1
intCnt = intCnt - 1 ' Decrement the max replace counter
Loop Until intCnt = 0
Replace = strWork
End Function
--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
[quoted text clipped - 12 lines]
A97 has a Replace function - it just must be called from VBA not from queries,
an oversight corrected in later versions.
