Thread: Line breaks
View Single Post
  #4  
Old July 13th, 2009, 02:33 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Line breaks

Well, the query example I gave does the right thing - I think!

replace(address,',',',' & chr(13))

should replace each comma in the field "address" by a comma followed by
a carriage return character. If you are actually seeing "char(13) or
"chr(13)" in the text, then you may have put quotes around 'char(13)'
that should not be there.

If you do not need to retain the comma characters you could use this:

replace(address,',',chr(13))

If that doesn't make sense to you can you step through how you were
actually trying to do the comma replacement?

Peter Jamieson

http://tips.pjmsn.me.uk

GDP wrote:
Dear Peter

Thanks for taking the time to reply. I don't have any problem with repacing
the commas with a different character, my problem is what character(s) to
replace it with.

Eg. In my address field in the Access DB I have: 21 North Street, Brighton, UK

If I replace the commas with something else e.g: 21 North Street char(13)
Brighton char(13) UK, this is exactly what comes across into the Word doc. I
assume there must be some kind of delimiters to enclose the char(13) with so
that Word interprets as a line break rather than a text string - but what!!

Any ideas?

Regards, Gary

"Peter Jamieson" wrote:

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.