Find and Replace

[Wuddle]

I have an imported document each month that needs one of the fields changed into various abbreviations. The abbreviations remain the same and there will be no variations.
How can I create a macro to find and replace the exact same text each time and not come up with the requirement to keep presssing the OK button.
Thanks in advance

 

[parry]

On 2003-02-05 04:26, Wuddle wrote:
I have an imported document each month that needs one of the fields changed into various abbreviations. The abbreviations remain the same and there will be no variations.
How can I create a macro to find and replace the exact same text each time and not come up with the requirement to keep presssing the OK button.
Thanks in advance

Hi Wuddle. I suggest you create an update query to amend the records in the table. How many abbreviations do you have? Perhaps give a couple examples.

With an update query you can, for example, use an IIF expression to say if value = United States then abbreviate to USA. IIf can only handle limited (max 7 I think) variations though so if you have loads of abbreviations then this may not be the best way and using Code may be required.

Also with a macro things need to be in consistent locations so are you importing into the same table each time or a new table? I would recommend importing to the same table and using an import specification so that you can automate this as well presuming the data you are importing is always the same type.

 

[Russell Hauf]

You could also make a table of the value and its abbreviation. For example,

About @
Because b/c
Within w/in

So say you wanted to change the values in the first column to the ones in the second each month. Make a table of your changes (one column is the value you want to change, the second column is the value you want to change TO), then create a Make-Table query in which you link your monthly table to the "abbreviation" table (by the field you want to change). Then Select all fields from the monthly table except the one you want to change, and select the abbreviation field from the "abbreviation" table, and make your new table.

HTH,

Russell