Find and Replace Query

[Ahliank]

Hi all,

I need help as I want to use an update query to Find/Replace text within
records:
I have a list of text to find and replace with

ie :
(S) with Singapore
(HK) with Hongkong
(ID) with Indonesia
(TH) with Thailand
(JP) with Japan
etc.

Any helpful hints as to how to do this using and update query?

 

[bcap]

Is the text you wish to replace the entire contents of the field, or just a
string embedded somewhere in the field? If the latter, how many times might
it appear in the field? What version of Access?

 

[Ahliank]

It is the random string in the field, I can't say how many times it might
appers in the field, it may appear as follow :

ex :
Original Version will be shown like this :
Julius Caesar (S) United aka Pompey (JP) United
Result I expected :
Julius Caesar Singapore United aka Pompey Japan United

Currently I'm using Access 2000.

Any help would be most appreciated

Thank You,
Ahliank

 

[bcap]

Right, what you need is the Replace() function. Unfortunately this was new
in Access 2000 and it's implementation was a bit half-baked, so to get it to
work reliably in a query you might need to wrap it in a VBA function first
(n.b. this only applies to A2000, the built-in Replace function works fine
in later versions):

Function MyReplace(strExpression As String, _
strFind As String, _
strReplace As String, _
Optional lngStart As Long = 1, _
Optional lngCount As Long = -1, _
Optional lngCompare As Long = vbBinaryCompare) _
As String

MyReplace = Replace(strExpression, strFind, strReplace, _
lngStart, lngCount, lngCompare)

End Function

Then you can use it in query like this:

UPDATE some_table SET some_field = MyReplace(some_field,"(S)","Singapore")

 

[Ahliank]

Hi bcap,

The replace function is working now, but my concern is can i perform the one
time updating? I mean I don't have to run the replace query(UPDATE some_table
SET some_field = MyReplace(some_field,"(S)","Singapore")) repeatly.

Thanks and Regards,
Ahliank

 

[John Spencer]

You can try setting up a translation table with two fields
Abbreviation: OldValue
Expansion: NewValue

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE YourTable INNER JOIN TranslationTable
ON YourTable.SomeField Like "*" & TranslationTable.Abbreviation & "*"
SET YourTable.SomeField = MyReplace([SomeField],[Abbreviation],[Expansion])

This may or may not work. You may get an error message - "Must use an
updateable query" You can test and see.

Also, if the abbreviation is always in parentheses, make sure you include the
parentheses in the table. I doubt that you would want to end up with
something like the following because you replaced all the "s" characters with
"Singapore".

JuliuSingapore CaeSingaporear (Singapore) United aka Pompey (Japan) United

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ahliank]

Hi John,

It worked this time. Thanks a million!

and Thanks alot bcap..

You can try setting up a translation table with two fields
Abbreviation: OldValue
Expansion: NewValue

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE YourTable INNER JOIN TranslationTable
ON YourTable.SomeField Like "*" & TranslationTable.Abbreviation & "*"
SET YourTable.SomeField = MyReplace([SomeField],[Abbreviation],[Expansion])

This may or may not work. You may get an error message - "Must use an
updateable query" You can test and see.

Also, if the abbreviation is always in parentheses, make sure you include the
parentheses in the table. I doubt that you would want to end up with
something like the following because you replaced all the "s" characters with
"Singapore".

JuliuSingapore CaeSingaporear (Singapore) United aka Pompey (Japan) United

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi bcap,

The replace function is working now, but my concern is can i perform the one
time updating? I mean I don't have to run the replace query(UPDATE some_table
SET some_field = MyReplace(some_field,"(S)","Singapore")) repeatly.

Thanks and Regards,
Ahliank

 

[Ahliank]

Hi John,

It seem like I need some help for you again. I tried to remove the special
character ie ( . , ; : " ' ) but it's come back with error messages. Any
suggestions to remove those special characters?

 

[John Spencer]

More information please.
What is the error message?
What does the query look like?

What are you replacing the characters with? If you are using the table scheme
and have entered a quote mark as the old value what have you entered as the
new value? If nothing (null) then that is probably the problem.

One Option:
In design view of the table, change the field's allow Zero Length property to
Yes. Then enter the new value into the table as a space. Access should
remove the space character and set the field to a zero length string.

Second Option:
The other option would be to test the value of the replacement field and if it
is null replace the Null with a zero length string (""). You can do that
using the NZ function or using an IIF statement. Since you are already using
VBA functions, you might as well use the NZ function.

UPDATE YourTable INNER JOIN TranslationTable
ON YourTable.SomeField Like "*" & TranslationTable.Abbreviation & "*"
SET YourTable.SomeField =
MyReplace([SomeField],[Abbreviation],Nz([Expansion],""))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ahliank]

Thanks John, Your second option just work perfectly on my case. Thanks..

More information please.
What is the error message?
What does the query look like?

What are you replacing the characters with? If you are using the table scheme
and have entered a quote mark as the old value what have you entered as the
new value? If nothing (null) then that is probably the problem.

One Option:
In design view of the table, change the field's allow Zero Length property to
Yes. Then enter the new value into the table as a space. Access should
remove the space character and set the field to a zero length string.

Second Option:
The other option would be to test the value of the replacement field and if it
is null replace the Null with a zero length string (""). You can do that
using the NZ function or using an IIF statement. Since you are already using
VBA functions, you might as well use the NZ function.

UPDATE YourTable INNER JOIN TranslationTable
ON YourTable.SomeField Like "*" & TranslationTable.Abbreviation & "*"
SET YourTable.SomeField =
MyReplace([SomeField],[Abbreviation],Nz([Expansion],""))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

It seem like I need some help for you again. I tried to remove the special
character ie ( . , ; : " ' ) but it's come back with error messages. Any
suggestions to remove those special characters?

 

[Ahliank]

Hi John,

I'm sorry but i got another problem.. Can I capture the value of
(Space)Co(Space) Instead of Co?? I want to replace a string like Connie & Co
I just want to replace The "Co" but instead of replace "Co" only it replace
the both "Co"nnie and "Co" itself. I hope my question is clear enough..
Thanks..

More information please.
What is the error message?
What does the query look like?

What are you replacing the characters with? If you are using the table scheme
and have entered a quote mark as the old value what have you entered as the
new value? If nothing (null) then that is probably the problem.

One Option:
In design view of the table, change the field's allow Zero Length property to
Yes. Then enter the new value into the table as a space. Access should
remove the space character and set the field to a zero length string.

Second Option:
The other option would be to test the value of the replacement field and if it
is null replace the Null with a zero length string (""). You can do that
using the NZ function or using an IIF statement. Since you are already using
VBA functions, you might as well use the NZ function.

UPDATE YourTable INNER JOIN TranslationTable
ON YourTable.SomeField Like "*" & TranslationTable.Abbreviation & "*"
SET YourTable.SomeField =
MyReplace([SomeField],[Abbreviation],Nz([Expansion],""))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

It seem like I need some help for you again. I tried to remove the special
character ie ( . , ; : " ' ) but it's come back with error messages. Any
suggestions to remove those special characters?

 

[John Spencer]

This gets tougher to do using a table based solution since Access trims off
trailing spaces.

You could try entering
space + CO + underscore in the update to field " CO_"
and then using the replace function to change the underscore character to a
space.

UPDATE YourTable INNER JOIN TranslationTable
ON YourTable.SomeField
Like "*" & MyReplace(TranslationTable.Abbreviation," ","_") & "*"
SET YourTable.SomeField =
MyReplace([SomeField],[Abbreviation],MyReplace(Nz([Expansion],""),"_"," "))

Another (and probably better) option would be to always require a leading and
trailing space in the replace. In this case you would not enter " CO _" as the
the item (abbreviation) to be replaced. You would use just "CO". To make
this work you would need to
-- add a leading and trailing space to the field you are replacing
-- add a leading and trailing space to the Abbreviation
-- add a leading and trailing space to the expansion
-- trim any leading and trailing spaces from the result

UPDATE YourTable INNER JOIN TranslationTable
ON YourTable.SomeField Like "*" & TranslationTable.Abbreviation & "*"
SET YourTable.SomeField =
Trim(MyReplace(" " & [SomeField] & " "," " & [Abbreviation] & " "," " &
Nz([Expansion],"") & " "))

As usual, BACK UP YOUR DATA before you try this.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ahliank]

Hi John,

I just can't add any trailing space to the field. It's look like every time
i added a trailing space to the field it'll be trims off the trailing space
instantly after i moved from that field.Anything wrong with my field
setting's?

If the table based solution is impossible to update this database can you
show me alternative way on doing the updating job?

This gets tougher to do using a table based solution since Access trims off
trailing spaces.

You could try entering
space + CO + underscore in the update to field " CO_"
and then using the replace function to change the underscore character to a
space.

UPDATE YourTable INNER JOIN TranslationTable
ON YourTable.SomeField
Like "*" & MyReplace(TranslationTable.Abbreviation," ","_") & "*"
SET YourTable.SomeField =
MyReplace([SomeField],[Abbreviation],MyReplace(Nz([Expansion],""),"_"," "))

Another (and probably better) option would be to always require a leading and
trailing space in the replace. In this case you would not enter " CO _" as the
the item (abbreviation) to be replaced. You would use just "CO". To make
this work you would need to
-- add a leading and trailing space to the field you are replacing
-- add a leading and trailing space to the Abbreviation
-- add a leading and trailing space to the expansion
-- trim any leading and trailing spaces from the result

UPDATE YourTable INNER JOIN TranslationTable
ON YourTable.SomeField Like "*" & TranslationTable.Abbreviation & "*"
SET YourTable.SomeField =
Trim(MyReplace(" " & [SomeField] & " "," " & [Abbreviation] & " "," " &
Nz([Expansion],"") & " "))

As usual, BACK UP YOUR DATA before you try this.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

I'm sorry but i got another problem.. Can I capture the value of
(Space)Co(Space) Instead of Co?? I want to replace a string like Connie & Co
I just want to replace The "Co" but instead of replace "Co" only it replace
the both "Co"nnie and "Co" itself. I hope my question is clear enough..
Thanks..

:

 

[John Spencer]

Access is helpful and always trims trailing spaces from a field when the data
is entered using the keyboard. You can force a trailing space in a field if
you use a query to populate the field. However, you are better off working
with the normal behavior.

I gave you a couple of alternatives as a possible solution. You would have to
use the first alternative - substitute an underscore character for trailing
spaces - if your substitutions must take place in the middle of a word or phrase.

If you only want to replace whole words (a space on both sides) then you would
need to use the second solution - your table would not include leading or
trailing spaces.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

I just can't add any trailing space to the field. It's look like every time
i added a trailing space to the field it'll be trims off the trailing space
instantly after i moved from that field.Anything wrong with my field
setting's?

If the table based solution is impossible to update this database can you
show me alternative way on doing the updating job?

This gets tougher to do using a table based solution since Access trims off
trailing spaces.

You could try entering
space + CO + underscore in the update to field " CO_"
and then using the replace function to change the underscore character to a
space.

UPDATE YourTable INNER JOIN TranslationTable
ON YourTable.SomeField
Like "*" & MyReplace(TranslationTable.Abbreviation," ","_") & "*"
SET YourTable.SomeField =
MyReplace([SomeField],[Abbreviation],MyReplace(Nz([Expansion],""),"_"," "))

Another (and probably better) option would be to always require a leading and
trailing space in the replace. In this case you would not enter " CO _" as the
the item (abbreviation) to be replaced. You would use just "CO". To make
this work you would need to
-- add a leading and trailing space to the field you are replacing
-- add a leading and trailing space to the Abbreviation
-- add a leading and trailing space to the expansion
-- trim any leading and trailing spaces from the result

UPDATE YourTable INNER JOIN TranslationTable
ON YourTable.SomeField Like "*" & TranslationTable.Abbreviation & "*"
SET YourTable.SomeField =
Trim(MyReplace(" " & [SomeField] & " "," " & [Abbreviation] & " "," " &
Nz([Expansion],"") & " "))

As usual, BACK UP YOUR DATA before you try this.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

I'm sorry but i got another problem.. Can I capture the value of
(Space)Co(Space) Instead of Co?? I want to replace a string like Connie & Co
I just want to replace The "Co" but instead of replace "Co" only it replace
the both "Co"nnie and "Co" itself. I hope my question is clear enough..
Thanks..

: