Find and Replace multiple values in one field

[Der Musensohn]

I have a field "state" as part of an address database (MS Access) over which
I do not have control of the data entry else I wouldn't have this problem! I
need to export the state field in the official two-letter abbreviation. The
data is entered in a variety of abbreviations and full names. Is there a
SINGLE query, macro or code available where I can take care of the most
common mistakes with a single click of a button? I have some experience with
macros but I would need some guidance as to where to copy and paste code
(i.e. I know where "on click" is under properties).
Thank you.

 

[Klatuu]

Is this export going to be done with a query?

 

[Der Musensohn]

Yes. The export eventually ends up in QuickBooks but that's an entirely
different issue. Right now, I'm copying the displayed query and pasting into
Excel because of the extra formatting needed for QB. I'm also considering
doing a mail merge to a table that sets up the spacial (not special)
formatting needed in QB and then pasting to Excel. But that's after I figure
out this issue.

 

[Klatuu]

You can export a query to an Excel Spreadsheet using TransferSpreadsheet.
You can find detail information in Help.

As to translating the free form field to the standard two character
abbreviation, I don't know of a utility that is readily available to do that;
however, I do have an idea that may help.

That would be to create a translation table that would have two fields. One
to carry all the possible variations of how the state may be presented and
one with the two character representation.
tblStateTranslation
STATE_NAME - The free form text styles
STATE_ABBR - The Two Letter abbreviation

Join this table to you your query on STATE_NAME with the state field in the
table you want to translate, but don't include it in the query output,
instead, include STATE_ABBR. That way, no matter what is in STATE_NAME, the
query will output the abbreviation.

You could build the table based on the variations you have currently. In
case the state field in the current table uses the correct abbreviation, you
would have to include a row in the translation table with that value. For
example, for Texas you might need:

STATE_NAME STATE_ABBR
TX TX
TEX TX
TEXAS TX
TEXIS TX
TAXES TX

I would also suggest an edit query that would match what is in the main
table with what is in the translation table to see if any that don't match
show up so you can edit the translation table before you actually output the
spreadsheet.