469,331 Members | 6,494 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

Rich Text Find Replace Problems

Hi All,

Environment is VBA code in Access 2007.

I am having some problems performing a find and replace on text that originates from a memo field in a table. The code is supposed to take internal document tracking numbers and replace them with simpler document number in reports for external clients, so the search string, or 'delimiter' is ~10 characters depending on document name.

I have used several methods involving the VBA functions: InStr; Split; and Replace. None reliably find the Search String within the test to be searched. ie. The find / replace methods work on some rich text fields, but not those that originate from memo fields.

I have tried passing variable ByVal into the find replace function, using String variables, both local and global, putting the text to be searched into text boxes on a form and processing them there before returning them to the table field and nothing I can see works.

Example memo text snippet:

"<div><strong>Wat‐1.A.1: </strong>Completed calculator. The shower usage calculation is adjusted according to bla bla bla.<br><strong>Wat‐1.A.2: </strong>drawings showing all installed fixtures</div>"

Searching for "Wat‐1.A.1"
Replacing with "Wat-1.1"

Example of function used to find replace:

Expand|Select|Wrap|Line Numbers
  1. Public Function FindReplace(ByVal fStr, ByVal rStr, ByVal sStr As String) As String
  2.  
  3.     Dim varSplit As Variant
  4.     Dim i As Integer
  5.  
  6.     If fStr = rStr Then
  7.         Exit Function
  8.     End If
  9.  
  10.  
  11.     Do While InStr(1, sStr, fStr, 1) <> 0
  12.  
  13.         varSplit = Split(sStr, fStr)
  14.  
  15.         sStr = varSplit(0) & rStr & varSplit(1)
  16.         i = i + 1
  17.     Loop
  18.  
  19.     Debug.Print ("Number of replacements: " & i & ". Search String: " & fStr)
  20.  
  21.     FindReplace = sStr
  22.  
  23. End Function
  24.  
  25.  
Or with a different function via text boxes on form:

Expand|Select|Wrap|Line Numbers
  1. Public Function FR_vForm(ByVal fStr, ByVal rStr, ByVal sStr As String)
  2.  
  3. Dim i As Integer
  4. Dim tStr As String
  5.  
  6. tStr = ""
  7.  
  8. With Forms![Cover Sheet Generator]
  9.  
  10.     .text_search = sStr
  11.     .text_find = fStr
  12.     .text_replace = rStr
  13.     i = 0
  14.     Do While .text_search <> tStr
  15.         tStr = .text_search
  16.         .text_search = Replace(.text_search, fStr, rStr)
  17.         i = i + 1
  18.     Loop
  19.  
  20.  
  21.     .text_output = FindReplace(fStr, rStr, sStr)
  22.     FR_vForm = .text_search
  23. End With
  24.  
  25. End Function
  26.  
Similarly the InStr function always returned 0 even when it was clear that there was more than one occurance of the search string within th text to be searched.
Jan 20 '11 #1
4 4421
mshmyob
904 Expert 512MB
Just subscribing.

Will check back in tomorrow if you get no response by then.

cheers,
Jan 20 '11 #2
mshmyob
904 Expert 512MB
Ok here is a way to replace a string in a memo field using recordsets.

Code for the form:

Expand|Select|Wrap|Line Numbers
  1. Dim strOrig As String
  2. Dim strAltered As String
  3. Dim strOld As String
  4. Dim strNew As String
  5.  
  6. Set rst = Me.RecordsetClone
  7.  
  8. rst.MoveFirst
  9. ' ------ put code in here to find the record you want -----
  10.  
  11. ' once you have moved to the proper record then do the following to replace a string in a memo field
  12.  
  13.  
  14. ' the memo field you want to look in for the string
  15. strOrig = rst.DataMemo
  16. 'the string you want to replace
  17. strOld = "Wat-1.A.1"
  18. 'what you want to put in place of the old string
  19. strNew = "Wat-1-A-1"
  20. 'call the function to search and replace
  21. strAltered = fSearchReplace(strOrig, strOld, strNew)
  22. ' update the memo field
  23. ' set the recordset into edit mode
  24. rst.Edit
  25. ' replace the memo field with the new data
  26. rst.DataMemo = strAltered
  27. ' update the recordset and close it
  28. rst.Update
  29. rst.Close
  30.  
Code for the module:

Expand|Select|Wrap|Line Numbers
  1. Public Function fSearchReplace(strOrig As String, strOld As String, strNew As String)
  2. Dim intCount As Integer
  3.  
  4. strAltered = ""
  5.  
  6. For intCount = 1 To Len(strOrig)
  7.     If Mid(strOrig, intCount, Len(strOld)) = strOld Then
  8.         strAltered = strAltered & strNew
  9.         intCount = intCount + (Len(strOld) - 1)
  10.     Else
  11.         strAltered = strAltered & Mid(strOrig, intCount, 1)
  12.     End If
  13. Next intCount
  14.     fSearchReplace = strAltered
  15. End Function
  16.  
If you have any questions please ask.

cheers,
Jan 21 '11 #3
Thanks for your help mshmyob.

Unfortunately it still doesn't work!!!! I made a little mod to your code to add some debugging, so here is the code and output:

Expand|Select|Wrap|Line Numbers
  1. Public Function fSearchReplace(ByVal strOrig As String, ByVal strOld As String, ByVal strNew As String)
  2. Dim intCount As Integer
  3. Dim strAltered, test As String
  4.  
  5. strAltered = ""
  6.  
  7. For intCount = 1 To Len(strOrig)
  8.  
  9.     If strOld = "Wat-1.A.1" Then
  10.         test = Mid(strOrig, intCount, Len(strOld))
  11.         Debug.Print ("'" & test & "'  Looking for: " & strOld & " StrCmp output: " & StrComp(test, strOld, 0))
  12.  
  13.     End If
  14.  
  15.     If Mid(strOrig, intCount, Len(strOld)) = strOld Then
  16.         Debug.Print ("replacement made")
  17.         strAltered = strAltered & strNew
  18.         intCount = intCount + (Len(strOld) - 1)
  19.     Else
  20.         strAltered = strAltered & Mid(strOrig, intCount, 1)
  21.     End If
  22. Next intCount
  23.     fSearchReplace = strAltered
  24. End Function
  25.  
The debug.print output is as follows as it parses a text region containing rich text formatting around the search string. Note that the StrComp function never returns 0 despite the strings matching exactly as far as I can see. I have tried to change the comparison method, and use functions such as UCase on both strings to try to get a mach. No dice.

'rong>Wat-' Looking for: Wat-1.A.1 StrCmp output: 1
'ong>Wat-1' Looking for: Wat-1.A.1 StrCmp output: 1
'ng>Wat-1.' Looking for: Wat-1.A.1 StrCmp output: 1
'g>Wat-1.A' Looking for: Wat-1.A.1 StrCmp output: 1
'>Wat-1.A.' Looking for: Wat-1.A.1 StrCmp output: -1
'Wat-1.A.1' Looking for: Wat-1.A.1 StrCmp output: 1
'at-1.A.1:' Looking for: Wat-1.A.1 StrCmp output: 1
't-1.A.1: ' Looking for: Wat-1.A.1 StrCmp output: 1

I canot see any reason why these two strings are unequal, however StrComp, InStr, Split and Replace cannot match the string.

Again, the method worked fine on the non field derived text field.

I am at a complete loss on this.

Cheers,

Elliot
Jan 24 '11 #4
mshmyob
904 Expert 512MB
Could you attach the form and the table so I can look at it and maybe I can spot something. I use the code I supplied all the time and it works (even on RTF memo fields), so I am at a loss until I see the complete steps you are taking.

cheers,
Jan 24 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by PC User | last post: by
6 posts views Thread by Brad Allison | last post: by
1 post views Thread by Jim Campau | last post: by
2 posts views Thread by pooba53 | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
reply views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.