Find and replace in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI,

I need to remove some charactors in a table. it is always the same ' I need
to replace but the value is repeated several times.

I need a way to remove all of these values and remove another prefix so that
the remainder of the field matches a second table I can then lookup from.
 
I have and it's not there. I can finf the find function but not the replace
function.

I under stood it to be docmd.replace("string","find",replacement") which
would have been replace("'","'","") used in a query as an expression but this
does not work
 
What version of Access are you using? (The Replace function wasn't
introduced until Access 2000)

The Replace function isn't used in conjunction with DoCmd.

You either need to assign its output to a variable:

strMyVar = Replace(strMyVal, "find", "replace")

or you need to use it in a query.
 
I'm stuck with 97. I've been trying every way I can think of to get around
this but I can't get my tables to match up until I remove this data
 
Here's what I use in Access 97:

Public Function MyReplace( _
ByVal ExpressionToSearch As Variant, _
ByVal FindString As String, _
ByVal ReplaceString As String, _
Optional ByVal Start As Long = 1, _
Optional ByVal MaxChanges As Long = -1, _
Optional ByVal CompareType As Long = vbBinaryCompare _
) As String

' This code was originally written by
' Doug Steele, MVP (e-mail address removed)
'
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: Provides the same functionality as the Replace
' function which is included in Access 2000 and newer.
'
' Inputs: ExpressionToSearch Required. String expression containing
' substring to replace.
' FindString Required. Substring being searched for.
' ReplaceString Required. Replacement substring.
' Start Optional. Position within expression
where
' substring search is to begin.
' If omitted, 1 is assumed.
' MaxChanges Optional. Number of substring
substitutions
' to perform.
' If omitted, the default value
is -1,
' which means make all possible
' substitutions.
' CompareType Optional. Specifies the type of string
' comparison. Valid values are:
' vbBinaryCompare (0)
' vbTextCompare (1)
' vbDatabaseCompare (2)
' The default is to perform a
binary
' comparison.
' Specify vbTextCompare (1) to
perform
' a textual, noncase-sensitive
comparison.
' Specify vbDatabaseCompare (2)
to perform
' a comparison based on
information
' contained in the database.
'
' Returns: A string where all occurrences of FindString have been
replaced by
' ReplaceString. The following rules apply:
' If Replace returns
' ExpressionToSearch is zero-length Zero-length string
("")
' ExpressionToSearch is Null Zero-length string
' FindString is zero-length Copy of
ExpressionToSearch
' ReplaceString is zero-length Copy of
ExpressionToSearch with all
' occurrences of
FindString removed
' Start > Len(ExpressionToSearch) Zero-length string
' MaxChanges = 0 Copy of
ExpressionToSearch

On Error GoTo Err_MyReplace

Dim booContinue As Boolean
Dim lngCount As Long
Dim lngFindLength As Long
Dim lngReplaceLength As Long
Dim lngSearchFrom As Long
Dim lngPos As Long
Dim strToSearch As String

' Check to make sure that the input parameters make sense.

strToSearch = ExpressionToSearch & vbNullString
If Len(strToSearch) = 0 Then
booContinue = False
Else
If Len(FindString) = 0 Then
booContinue = False
Else
If MaxChanges = 0 Then
booContinue = False
Else
If Start > Len(strToSearch) Then
strToSearch = vbNullString
booContinue = False
Else
booContinue = True
End If
End If
End If
End If

' We've checked all the simple cases.
' Do the actual replacing (if necessary)

If booContinue Then
lngSearchFrom = Start
lngFindLength = Len(FindString)
lngReplaceLength = Len(ReplaceString)
lngPos = InStr(lngSearchFrom, strToSearch, _
FindString, CompareType)
Do While lngPos > 0
strToSearch = Left$(strToSearch, lngPos - 1) & _
ReplaceString & _
Mid$(strToSearch, lngPos + lngFindLength)
lngCount = lngCount + 1
If MaxChanges <> -1 And _
lngCount >= MaxChanges Then
Exit Do
End If
lngPos = InStr(lngPos + lngReplaceLength, _
strToSearch, FindString, CompareType)
Loop
End If

End_MyReplace:
MyReplace = strToSearch
Exit Function

Err_MyReplace:
Err.Raise Err.Number, "Replace", Err.Description
Resume End_MyReplace

End Function

Hopefully that'll survive line-wrapping!
 
Back
Top