Find And Replace

[Tim]

Hi folks,

I found a find and replace function in Help(Access 97).
I tried to use it to replace "DB" by "database" in one of
my module.

But it didn't do what I expected. It replaced all
the "DB" by "database".

Old_String New_String
strDB strdatabase
DB database
DBDate databaseDate


I only want it change DB to database not the rest of them.

Could anyone show me how to modiy the function?

Thanks in advance.

Tim.

 

[John Spencer (MVP)]

Check the Find Whole Word Only box in the dialog. That should do it.

 

[Tim]

John,

Thanks for your response. I think my question is not
clear.

I found a find and replace function (module) in Access 97
HELP that can find and replace string in module.

I need a help to modify that function to search the whole
word only.

Could you help me out?

Thanks again.

Tim.

 

[John Vinson]

I need a help to modify that function to search the whole
word only.

If you want to replace all the words FOO with BAR, you can use the
function to replace

" FOO " with " BAR "

and (depending on the context) similar replacements with various
punctuation marks in place of the spaces.

OTOH, it might be worth springing a few bucks for one of the
third-party tools which do all this work for you:

 

[John Vinson]

On Mon, 1 Mar 2004 20:47:45 -0800, "Tim"

sorry... sent previous message prematurely. Some replacement tools (I
believe all still support A97):

Free:
http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

 

[John Spencer (MVP)]

If you are only doing find and replace in the vba module code, why bother to use
a find and replace function?

However, here is some vba code that returns the position in a string of a string
by whole words. This will probably wrap lines in the newsreader. It also if I
remember correctly only returns the position of the first match.

Perhaps you can modify this to do what you want.

Public Function FindWholeString(strSearch, strFind) As Long
'*******************************************
'Name: FindWholeString (Function)
'Purpose: Looks in a string to see
' if it contains a second string as a whole word
'Author: John Spencer UMBC-CHPDM
'Date: April 19, 2000, 02:10:35 PM
'Called by:
'Calls:
'Inputs: strSearch as string to be examined, strFind as string to be located
'Output: >0 indicates string found, 0 or < 0 indicates string not found
'*******************************************
'What Characters do we want to delimit whole word
Const strDelimiters As String = "[] ().!, """

Dim lngPos As Long
Dim tfStart As Boolean, tfEnd As Boolean
'Is found text delimited with indicator of whole word

lngPos = InStr(strSearch, strFind)

If lngPos > 0 Then 'String is there, now let's see if it is a whole word
If lngPos = 1 Then 'At beginning of string so tfStart is true
tfStart = True
Else
tfStart = InStr(strDelimiters, _
Mid(strSearch, lngPos - 1, 1)) > 0
End If

If lngPos + Len(strFind) = Len(strSearch) Then
'at end of string so tfStart is true
tfEnd = True
Else
tfEnd = InStr(strDelimiters, _
Mid(strSearch, lngPos + Len(strFind), 1)) > 0
End If
End If

'Recursive call to look for next occurence
'if last occurence did not match as whole word
If lngPos > 0 And Not (tfStart And tfEnd) Then
lngPos = lngPos * -1
lngPos = FindWholeString(Mid(strSearch, _
Abs(lngPos) + Len(strFind)), strFind)
End If


'tfReturn = tfStart And tfEnd And lngPos > 0

FindWholeString = lngPos

End Function