Access to Word mail merge~ preserve format

Factual Questions
1

Well, Access (2000) has stumped me again with one of those simple problems that should be easy to figure out. Of course, the operative word is should. I just haven’t seen how to preserve the capitalization of words when I use a query to create a mailing list.

The database tracks, among other things, name and address. I am a lazy typist, and don’t like getting slowed down by the shift key. On the fields for first and last name and city, I have a simple after update event of “Me.[FIELD].Value = StrConv([FIELD], vbProperCase)”. Works great. However, in the state field (on both the form and the table) I have a “>” to force all caps. The mail merge is based on a query, not a table.

The problem is that when I create the new document in Word, the names and cities are all properly capitalized, but the state is not. I have a few hundred records to merge (the first of many batches) and the capitalization of the state has reverted to whatever the user originally entered.

Is there a way to force caps within a Word merge field? Is there a way to preserve the formatting from Access? I realize that I can use the update event with a vbUpperCase, but that won’t help with the records that are already updated (unless I want to revisit every record). Also, there are some fields that I may wish to capitalize in some places, but not in others. Any suggestions?
Thanks!

Rhythmdvl

2

The formatting only affects the display of the data, not the underlying data itself. Therefore, your Word document is using the data returned from the query.

You have several options:

  1. Write an Update Query to modify all the existing data in your table(s). This can change all the lowercase data in the state field to uppercase all at once.

  2. Create a calculated field in your query. Something like:

StateName:ucase([State])

Word will now use the data from the calculated field when you merge with this query.

  1. What you REALLY should do is have a separate table for State Abbreviations, then relate your table to it. Only allow the user to choose a valid entry from this table. Users then cannot screw up your data by entering invalid state names or abbreviations, and the data is structured properly.

Let me know if any of this doesn’t make sense.