How ban I change words in a field?

[Dimitris]

Hello to all.
What I need is this. I have a field (F1) in a table (T1) in which data as
sentences are entered. I need to change some words in these sentences for
example, in any entry that has the words "European Union" I want to change
it with: "EU". For example there may be an entry: "Member of the European
Union Club" I want it to become "Member of the EU club". Is it possible? If
yes please give me step by step instructions since I am new in Access.

Thanks in advance
Dimitris

 

[Guest]

it's certainly possible. the best practical way to do it probably depends on
the number of strings you are looking for.

if there are only a few strings you're looking for, this might work for you.

Private Sub MyField_AfterUpdate()
Me!MyField = Replace(Me!MyField, "European Union", "EU")
Me!MyField = Replace(Me!MyField, "United Nations", "UN")
'and so forth
End Sub

however if you have a lot of strings to check this will slow you down as it
will be inefficient (but I can't think of a better way offhand)

 

[Guest]

Create a query based on T1 with F1 as the only column. Change the query type
to an Update query and then in the Update To row of your column put
Replace([F1],"European Union","EU")
You might like to take a backup copy of the T1 table before you run this
query just in case.

 

[Guest]

sorry... more step by step.
1) go into design view of your form.
2) select the field you wish to change the data in.
3) bring up the properties box.
4) click on the ... button next to the 'after update' in the events tab
5) choose code builder
6) THEN put in the code as per my previous post

 

[Dimitris]

Thanks for your replies Jack and Dennis.
First of all I do not have a form. The table is done we just need to change
specific words.
Also Dennis please tell me, this will change just those words right? No
matter what other words are in the entry (sentence)?
Thanks
Dimitris

 

[Guest]

Correct, it will only change those words and retain any text before and any
text after.

Thanks for your replies Jack and Dennis.
First of all I do not have a form. The table is done we just need to change
specific words.
Also Dennis please tell me, this will change just those words right? No
matter what other words are in the entry (sentence)?
Thanks
Dimitris

 

[DL]

A minor point, its not a terribly good idea to add/edit records on the tbl
directly, particularly if users are adding data. A Form should be used.

Thanks for your replies Jack and Dennis.
First of all I do not have a form. The table is done we just need to change
specific words.
Also Dennis please tell me, this will change just those words right? No
matter what other words are in the entry (sentence)?
Thanks
Dimitris

 

[John Vinson]

Thanks for your replies Jack and Dennis.
First of all I do not have a form. The table is done we just need to change
specific words.
Also Dennis please tell me, this will change just those words right? No
matter what other words are in the entry (sentence)?

Jack's code will not affect data which has already been added to the
table - only newly entered data being loaded via the form.

In order to change the existing data you need to run an Update query
instead. BACK UP YOUR DATABASE FIRST - update queries are irreversible
and you don't want to clobber

Create a new Query based on your table. Select the field containing
the text that you want to edit (I'll assume it's named MyField); to
avoid inefficiency, put

LIKE "*European Union*"

on the Criteria line so it will only edit those records which in fact
need to be edited.

Change the query to an Update query using the Query menu option, or
the query type tool on the toolbar.

On the Update To line type

= Replace([myfield], "European Union", "EU")

Run the query by clicking the ! icon. Check your table to see that
it's worked correctly, and if not... get out your backup!

John W. Vinson[MVP]