[GDP]

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.

[Peter Jamieson]

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.

[GDP]

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.

[Peter Jamieson]

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.

[GDP]

Dear Peter

I'm not actually trying to do a replace - The list of addresses is generated
from a feed from a web site, so I can programatically separate the lines by
whatever I like as I pull in the data. The problem I have is that as the data
comes over from the Access database into the word document, it shows these
characters in the text of the address.

I've tried enclosing with ", ', {} to no avail!

Any more thoughts would be much appreciated.

Regards, Gary


"Peter Jamieson" wrote:

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.

[Peter Jamieson]

So let's try to get this straight:
a. you are getting data from a web site and putting it into Access
b. you are using that Access database as the data source for your merge.

If that isn't the case, please elaborate. If it is, what is now in the
address field in the Acces database that you are using as the data source?

Peter Jamieson

http://tips.pjmsn.me.uk

GDP wrote:
Dear Peter

I'm not actually trying to do a replace - The list of addresses is generated
from a feed from a web site, so I can programatically separate the lines by
whatever I like as I pull in the data. The problem I have is that as the data
comes over from the Access database into the word document, it shows these
characters in the text of the address.

I've tried enclosing with ", ', {} to no avail!

Any more thoughts would be much appreciated.

Regards, Gary


"Peter Jamieson" wrote:

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.

[Doug Robbins - Word MVP]

Well, the obvious thing to separate them by would be a carriage return.
Assuming that is not possible, then your only option is that suggested by
Peter. That is create a query in Access that selects the fields from the
table that you are now using in Access and in that query, use the Replace()
function to replace the commas that are now separating the components of the
address with carriage returns (Chr(13)). You then use that query as the
data source for your mail merge.

That is you need to do a replace as part of the process.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"GDP" wrote in message
...
Dear Peter

I'm not actually trying to do a replace - The list of addresses is
generated
from a feed from a web site, so I can programatically separate the lines
by
whatever I like as I pull in the data. The problem I have is that as the
data
comes over from the Access database into the word document, it shows these
characters in the text of the address.

I've tried enclosing with ", ', {} to no avail!

Any more thoughts would be much appreciated.

Regards, Gary


"Peter Jamieson" wrote:

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.