ACC97: Search and Replace in Large Memo Field Causes IPF

RESOLUTION

1. Create a module and type the following line in the Declarations section if it is not already there:
   

         Option Explicit
   						

2. Type the following procedure:
   

       Function ReplaceString(strSearch As String, strSearchFor As String, _
          strReplaceWith As String)
       On Error GoTo err_ReplaceString
           ' Searches the strSearch variable for strSearchFor
           ' and replaces it with strReplaceWith.
           Dim lngFoundLoc As Long    ' Location of match.
           Dim lngLenRemove As Long   ' Length of string being replaced.
           ' Set length of original text to skip.
           lngLenRemove = Len(strSearchFor)
           ' Set location of match.
           lngFoundLoc = InStr(1, strSearch, strSearchFor)
           ' If strSearchFor isn't found in strSearch
           ' just return the original string.
           If lngFoundLoc = 0 Then
               ReplaceString = strSearch
           ' If match is found, return original string up to match
           ' location, concatenate new text, and search the rest of
           ' the string recursively for additional matches.
           Else
               ReplaceString = Left(strSearch, lngFoundLoc - 1) & _
                   strReplaceWith & _
                   ReplaceString(Mid(strSearch, lngFoundLoc + _
                   lngLenRemove), strSearchFor, strReplaceWith)
           End If
       exit_ReplaceString:
           Exit Function
       ' Print error to Debug window and don't interrupt query.
       err_ReplaceString:
           Debug.Print "Error Replacing String """ & _
               strSearchFor & """ with """ & _
               strReplaceWith & """ in text """ & _
               strSearch & """"
           ' If there is an error, return original string
           ' and exit the function.
           ReplaceString = strSearch
           Resume exit_ReplaceString
       End Function
   						

3. On the Debug menu, click "Compile and Save All Modules."
4. Create a new query and add the table containing the Memo field:
   

         Query: qryUpdateMemos
         --------------------------------------------------------------------
         Type: Update Query
   
         Field: <Name of Memo Field>
            Table: <Name of Table>
            Update To:
             ReplaceString([<Name of Memo Field>],[Search for],[Replace with])
             Criteria:
         Field: Instr(1,[<Name of Memo Field>], [Search for])
            Update To:
            Criteria: >0
   						

5. On the Query menu, click Parameters.
6. On the first line of the grid, type [Search for] in the Parameter field, and select Text from the Data Type box.
7. On the second line of the grid, type [Replace With] in the Parameter field, and select Text from the Data Type box.
8. Run the query. Enter the text to search for and the text to replace with when prompted.