Large Memo field search and replace error 
Author Message
 Large Memo field search and replace error

Hello,

I have never actually worked with code for modules in access, but I need
to build a module for the 'search and replace' bug in access 97. I got
the VBA code for the module from Microsoft Support Online, but when I
try to compile and save, it gives me a syntax error on the under score
character _ . Is this amn illegal character? If so, why is it in the
Microsoft work around for the large memo field bug? Please help.

Roy



Sat, 19 May 2001 03:00:00 GMT  
 Large Memo field search and replace error
The underscore character is often used as a line-continuation character when
documenting code.
If that is the case, you must remove the underscore character before
compiling.
Quote:

>I have never actually worked with code for modules in access, but I need
>to build a module for the 'search and replace' bug in access 97. I got
>the VBA code for the module from Microsoft Support Online, but when I
>try to compile and save, it gives me a syntax error on the under score
>character _ .



Sat, 19 May 2001 03:00:00 GMT  
 Large Memo field search and replace error
Below is the code in question, the Underscore Characters appear not only at the
end of a line, but also in various middle places. check it out:

   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

I hope you can read this as it was intended. it is basically one long paragraph.

Roy

Quote:

> The underscore character is often used as a line-continuation character when
> documenting code.
> If that is the case, you must remove the underscore character before
> compiling.


> >I have never actually worked with code for modules in access, but I need
> >to build a module for the 'search and replace' bug in access 97. I got
> >the VBA code for the module from Microsoft Support Online, but when I
> >try to compile and save, it gives me a syntax error on the under score
> >character _ .



Tue, 22 May 2001 03:00:00 GMT  
 Large Memo field search and replace error
Below is the code in question, the Underscore Characters appear not only at the
end of a line, but also in various middle places. check it out:

   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

I hope you can read this as it was intended. it is basically one long paragraph.

Roy

Quote:

> The underscore character is often used as a line-continuation character when
> documenting code.
> If that is the case, you must remove the underscore character before
> compiling.


> >I have never actually worked with code for modules in access, but I need
> >to build a module for the 'search and replace' bug in access 97. I got
> >the VBA code for the module from Microsoft Support Online, but when I
> >try to compile and save, it gives me a syntax error on the under score
> >character _ .



Tue, 22 May 2001 03:00:00 GMT  
 Large Memo field search and replace error
Dorman,

Thank you! That did it.

Roy

Quote:

> Roy, following is what I think the code might be after removing all the
> comments and attempting to reformat the lines properly. Note that the
> "ReplaceString =" assignment still has some _ characters because the
> expression is too long to fit on one line in a word processor. However, when
> inserting this code in a module you can delete the underline characters and
> the carriage return/line feed that follows so that the separate lines appear
> as one line in the module.

> Function ReplaceString(strSearch As String, strSearchFor As String,
> strReplaceWith As String)
>     On Error GoTo err_ReplaceString

>     Dim lngFoundLoc As Long
>     Dim lngLenRemove As Long

>     lngLenRemove = Len(strSearchFor)
>     lngFoundLoc = InStr(1, strSearch, strSearchFor)
>     If lngFoundLoc = 0 Then
>         ReplaceString = strSearch
>     Else
>         ReplaceString = Left(strSearch, lngFoundLoc - 1) & _
>                                        strReplaceWith & _
>                                        ReplaceString(Mid(strSearch,
> lngFoundLoc + lngLenRemove), _
>                                        strSearchFor, strReplaceWith)
>     End If

>     exit_ReplaceString: Exit Function
>     err_ReplaceString: Debug.Print "Error Replacing String """ &
> strSearchFor & _
>            """ with """ & strReplaceWith & """ in text """ & _
>            strSearch & """" ReplaceString = strSearch Resume
>     exit_ReplaceString

> End Function
> ________________________________________

> If you still can't get this to work then try my function which accomplishes
> the same purpose but is a bit more flexible.

> Function Replace(ByVal sInString As String, sFindString As String, _
>                           Optional sReplaceString As String = "", _
>                           Optional iCount As Integer = 1) As String

>   Dim iSpot As Integer, iCtr As Integer, iStart As Integer
>   iStart = 1; iCtr = iCount
>   Do While iCtr > 0 OR iCount = 0
>       iSpot = InStr(iStart, sInString, sFindString)
>       If iSpot > 0 Then
>          sInString = Left(sInString, iSpot - 1) & sReplaceString &
> Mid(sInString, iSpot + Len(sFindString))
>          iStart = iSpot + Len(sReplaceString) + 1
>          iCtr = iCtr - 1
>       Else
>          Exit Do
>       End If
>   Loop

>   Replace = sInString

> End Function

> NOTE: The first line can be one long line in the code module by removing the
> underscore characters and pressing the delete key enough times to remove the
> hidden CR/LF characters. But it's better reading to use the underscore, line
> continuations.

> It can be called in any of the following ways:

>     MyVar = Replace(MyVar, "George")
> Replaces first "George" with "". One is the default.

>     MyVar = Replace(MyVar,"George","Helen")
> Replaces first "George" with "Helen"

>     MyVar = Replace(MyVar, "George","Helen",10)
> Replace first 10 (or fewer) "George" with "Helen".

>     MyVar = Replace(MyVar, "George", , 0)
> Replaces all "George" with "". Note that passed 0 really means all.

>     MyVar = Replace(MyVar, "Once Upon a Time", Once Each Time,0)
> Guess what.

> dorman


> >Below is the code in question, the Underscore Characters appear not only at
> the
> >end of a line, but also in various middle places. check it out:

> >   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

> >I hope you can read this as it was intended. it is basically one long
> paragraph.

> >Roy


> >> The underscore character is often used as a line-continuation character
> when
> >> documenting code.
> >> If that is the case, you must remove the underscore character before
> >> compiling.


> >> >I have never actually worked with code for modules in access, but I need
> >> >to build a module for the 'search and replace' bug in access 97. I got
> >> >the VBA code for the module from Microsoft Support Online, but when I
> >> >try to compile and save, it gives me a syntax error on the under score
> >> >character _ .



Tue, 29 May 2001 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Search an replace in memo fields

2. Large Memo Field search fix from Microsoft

3. Searching an replacing in a large (ps)file

4. insert large data into memo field problem

5. Find/Replace in memo field.

6. Append memo field to a different memo field

7. Parsing and Replacing Throughout the Memo Field

8. Memo fields - problems reporting 2 memo fields

9. ADO/SQL - Updating large memo field

10. How can I search a Memo Field in code

11. Help - Searching Memo Fields For Multiple Strings

12. Searching Memo Field For Text Strings

 

 
Powered by phpBB® Forum Software