Search and Replace Part of a Field in a Query

[Brian Smith]

I have a query that contains a field which includes text from an open-ended
survey question. This is a text field and the only other field is an auto
number field. There are certain words and phrases I would like to clean up
by doing a search and replace. The problem is in some cases I do not want to
change the give word or phrase. I tried using an update query but I can't
figure out how to only change a given word/phrase (which can occur anywhere
in the text) while still keeping all of the other text as is.

For example, a person might have written:

museum, casino, zoo

I would like to change this to:

museum, Star Dog Casino, zoo

But if a person wrote:

Star Dog Casino, amusement park, zoo

I would like no changes to be made.

Is the above possible using an update query? In other words, no matter where
"casino" appears in the field have it replaced with "Star Dog Casino" unless
that term was used and if "casinos" was used I'd like to leave that as is as
well.

Trust the above makes sense.

Thanks.

Brian

 

[KARL DEWEY]

Your example shows 'casino' preceeded and followed by commas.
You can use this if that is the case throughout --
Replace([YourFieldName], ", casino, ", ", Star Dog Casino, ")

It includes the commas and spaces.

 

[John W. Vinson]

I have a query that contains a field which includes text from an open-ended
survey question. This is a text field and the only other field is an auto
number field. There are certain words and phrases I would like to clean up
by doing a search and replace. The problem is in some cases I do not want to
change the give word or phrase. I tried using an update query but I can't
figure out how to only change a given word/phrase (which can occur anywhere
in the text) while still keeping all of the other text as is.

For example, a person might have written:

museum, casino, zoo

I would like to change this to:

museum, Star Dog Casino, zoo

But if a person wrote:

Star Dog Casino, amusement park, zoo

I would like no changes to be made.

Is the above possible using an update query? In other words, no matter where
"casino" appears in the field have it replaced with "Star Dog Casino" unless
that term was used and if "casinos" was used I'd like to leave that as is as
well.

Trust the above makes sense.

Since it's free format text, this will almost surely involve a sophisticated
USB interface...

Using Someone's Brain.

You can *approach* it with a query like

UPDATE tablename
SET fieldname = Replace([fieldname], "casino", "Star Dog Casino")
WHERE fieldname LIKE "*casino[!S]*
AND fieldname NOT LIKE "*star dog casino*

but this will have limitations (for instance it does not allow for "casino" to
be the first or last entry in the field, and may cause unwanted replacements,
e.g. "visiting Montecasino on our European trip").

 

[Brian Smith]

Your example shows 'casino' preceeded and followed by commas.
You can use this if that is the case throughout --
Replace([YourFieldName], ", casino, ", ", Star Dog Casino, ")

It includes the commas and spaces.

This did most of what I wanted but obviously nothing will be perfect when
there are an infinite number of ways people could type things.

Thanks.

Brian

 

[Brian Smith]

The following function, which identifies a 'word' rather than a
substring,
will handle John's 'Monte Casino' type of situation (BTW its actually
Monte
Cassino)

Public Function FindWord(varFindIn As Variant, varWord As Variant) As
Boolean

Const PUNCLIST = " .,?!:;(){}[]"
Dim intPos As Integer

FindWord = False

If Not IsNull(varFindIn) And Not IsNull(varWord) Then
intPos = InStr(varFindIn, varWord)

' loop until no instances of sought substring found
Do While intPos > 0
' is it at start of string
If intPos = 1 Then
' is it whole string?
If Len(varFindIn) = Len(varWord) Then
FindWord = True
Exit Function
' is it followed by a space or punctuation mark?
ElseIf InStr(PUNCLIST, Mid(varFindIn, intPos +
Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
Else
' is it precedeed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos - 1, 1)) > 0 Then
' is it at end of string or followed by a space or
punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos +
Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
End If
End If

' remove characters up to end of first instance
' of sought substring before looping
varFindIn = Mid(varFindIn, intPos + 1)
intPos = InStr(varFindIn, varWord)
Loop
End If

End Function

You can call it in an 'update' query like so:

UPDATE [tablename]
SET [fieldname] = Replace([fieldname], "casino", "Star Dog Casino")
WHERE FindWord([fieldname], "casino")
AND [fieldname] NOT LIKE "*star dog casino*;

Be sure to back up the table first!

Apologies in advance if I don't reply to any further posts on this, but
I'm
away incommunicado for a week or so as of now.

Ken Sheridan
Stafford, England

Thanks for the code! I'll test it out and see how it works.

Brian