Hi,

I am trying to modify the contents of a mail merge field. Basicall, I
need to strip the first two characters and the last 5 characters from a
particular field:

e.g. "P:ABC123(NNN)" strip off P: and (NNN)

Unfortunately, the query that retrieves this data is automatically
generated and can't be modified. Any ideas?

Regards,
Tony

I wouldn't go so far as to say that it cannot be done during the merge
process if you are using XP or later, but it would be quite complex to
achieve it. You may however be able to do something after the merge using a
Wildcard Replace

See the article "Finding and replacing characters using wildcards" at:

http://word.mvps.org/FAQs/General/UsingWildcards.htm

Alternatively, you could initially use the original data source to create a
catalog or directory type mailmerge document and run a macro over that to
strip of the unwanted information and then use that document as the
mailmerge data source.
--
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
wrote in message
oups.com...
Hi,

I am trying to modify the contents of a mail merge field. Basicall, I
need to strip the first two characters and the last 5 characters from a
particular field:

e.g. "P:ABC123(NNN)" strip off P: and (NNN)

Unfortunately, the query that retrieves this data is automatically
generated and can't be modified. Any ideas?

Regards,
Tony

I am trying to modify the contents of a mail merge field. Basicall, I
need to strip the first two characters and the last 5 characters from a
particular field:

e.g. "P:ABC123(NNN)" strip off P: and (NNN)

Unfortunately, the query that retrieves this data is automatically
generated and can't be modified. Any ideas?

You'll need to either have that query modified, or import the data into
an application where you can modify it before using it for mail merge.
Into Excel, for example, where you could insert an additional column and
use functions on the data to return the result you want.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

ok, I've got a macro to do a wildcard search and replace, but I would
really like to try and get this to operate directly off merge fields.

I was thinking maybe if I used a database field to perform an sql query
on the existing connection, but I can't work out how to get the
database field to point to the existing connection. Is this possible?

Alternatively I was thinking if I there was some kind of temporary
variable I could populate, that would display on the page, then I could
use a MailMergeBeforeRecordMerge event to retrieve the field value and
use mid$ to achieve the inner text and write it to the temporary
variable. Is this possible?

I was thinking maybe if I used a database field to perform an sql query
on the existing connection, but I can't work out how to get the
database field to point to the existing connection. Is this possible?

You can't set up the database field so that it picks up the existing
connection (i.e. you can't specify a switch or something like "Current
Connection") - you have to specify the connection parameters in full. So if
the connection details need to change dynamically, you would need to use
code prior to the merge to get the correct connection details, build the
correct SQL, and insert or modify a suitable DATABASE field. You might find
that you have locking/concurrency problems with some data sources.

Alternatively I was thinking if I there was some kind of temporary
variable I could populate, that would display on the page, then I could
use a MailMergeBeforeRecordMerge event to retrieve the field value and
use mid$ to achieve the inner text and write it to the temporary
variable. Is this possible?

You can indeed use MailMergeBeforeRecordMerge to get the field value and use
mid$. all you then have to do is decide how to stuff the result into the
mail merge main document. Unfortunately you can't create a new data source
column dynamically so you can't use a MERGEFIELD field. If you use bookmarks
to specify where you need the text to go, you need to make sure that you
bookmark the text you insert so that you can delete it or overwrite it when
you process the /next/ record. In my view, if your text is simple (no weird
Unicode characters such as zero-width spacers and so on), and there is
always going to be some text to insert (even if it is only a single space),
then the simplest approach is to assign the text to a Word document
variable, use a { DOCVARIABLE } field where you want to insert it, and make
sure you update the field before the record is merged.

Peter Jamieson

wrote in message
oups.com...
ok, I've got a macro to do a wildcard search and replace, but I would
really like to try and get this to operate directly off merge fields.

I was thinking maybe if I used a database field to perform an sql query
on the existing connection, but I can't work out how to get the
database field to point to the existing connection. Is this possible?

Alternatively I was thinking if I there was some kind of temporary
variable I could populate, that would display on the page, then I could
use a MailMergeBeforeRecordMerge event to retrieve the field value and
use mid$ to achieve the inner text and write it to the temporary
variable. Is this possible?