Unfortunately, Word does not have a good set of functions for modifying
strings in its field language (there are some, e.g. for making text
upper case etc., but nothing that does the sort of "find/replace" you
want to do on an individual field. This particular problem would
probably be much more easily fixed by creating a query in Access that
replaces every comma in the address field by comma+CR. For example, if
your data source table/query is called mysource and the addresses are in
a column called address, then you could use

SELECT *,replace(address,',',',' & chr(13)) as newaddress
FROM mysource

and use that as the data source for your merge. Unfortunately, even that
has problems because you will not be able to connect to that query using
the default method used by Word 2002 and later (OLE DB). You have to
connect using DDE - that's the default in Word 2000 and earlier. In Word
2002 and 2003 you can specify DDE by checking Word
Tools-Options-General-Confirm conversions at open, then connecting to
your data source and specifying the DDE option in the extra dialog box
that appears. In Word 2007 there's a similar option in Word-Office
Button-Word Options-Advanced-General, but
a. if the database is a .accdb, you'll need to check "Show All" in the
extra dialog that appears
b. you may find DDE connectivity does not work well, or does not work
at all.

So another thing you can do is specify the QUERY as a CREATE TABLE query
that you then use as your data source. That's a bit of a pain because
you have to run the query prior to each merge, but it's probably
marginally easier than exporting the data from Access.


Peter Jamieson

http://tips.pjmsn.me.uk

GDP wrote:
I'm doing a mail merge from Access using the mail merge wizard. My address
field has the whole address in one field, separated by commas. I would like
to insert a line break after each comma, so that each line of the address
appears on a separate line in the Word doc.

I assume there is a special character I need to insert in the address field
after each comma to achieve this. I have tried a few (^p, ^|, chr(13), /n
etc), but when I do the mail merge it just displays those characters in the
word document. Any suggestions would be much appreciated.