I would like to pick up just a specific word, e.g. the first word, from the
contents of a Word mail merge field.
For example, if the mail merge field contains the name in the format
"LastName, FirstName", I want to just display the lastname.

Is there a way of doing this in a mail merge field? If not, is there a way
to call a custom macro from a field so that the formatting of the field can
be performed while the mail merge is executing?

Many thanks,
Mark

If possible, you need to do this in the data source. If it's Access, you can
write a query using the instr and left functions to do it. If it's Excel,
you could probably write a function to fill in a new column with the desired
value. For example, if the field containing the data is called myname, the
following SQL expression in an Access query should work:

left(myname,instr(myname+',',',')-1)

You only need the + ',' to deal with cases where myname does not contain a
comma.

If not, is there a way
to call a custom macro from a field so that the formatting of the field
can
be performed while the mail merge is executing?

There isn't a really simple way to do this that just uses Word fields, but
there are several methods that may work, depending on the version of Word
and the data source
a. the "official" method in Word 2002/2003 is probably to use VBA and the
MailMergeBeforeRecordMerge event to do some processing and stuff the result
into the Mail Merge Main document immediately before each record is merged.
This should be feasible regardless of the data source. You get the field
content using Doc.MailMerge.DataSource.Datafields("myname") and use much the
same expression as above to get the correct text.
b. for data sources that Word accesses via SQL dialects with functions such
as instr and left, you can construct a suitable formula in SQL and issue it
in the VBA OpenDataSource call. This should work with data sources
accessible via the Jet engine (Access .mdbs, Excel workbooks, even plain
text files and others) and SQL servers such as SQL Server and Oracle, but
won't work for data sources such as .doc files, .rtf files etc. which Word
accesses through a very simple dialect of SQL. For Jet or ODBC databases the
expression would probably be as above - other databases may have different
names and syntax to do instr and left.
c. you can use a DATABASE field to do the same calculation as in (a) or
(b). This is simpler than you might think but you need to test it to ensure
it works for you. In particular, you may keep getting the "this...will
execute an SQL query" message (I forget the text) which can only be fixed by
patching the registry. The easiest approach is probably to create a
completely empty Access database (i.e. it needs no queries or tables). Let's
say it is called d.mdb and is in c:\d. Then insert the following DATABASE
field by hand - this one works for Word 2002/2003:

{ DATABASE \d "c:\\d\\d.mdb" \c ""
\s "SELECT left('{ MERGEFIELD myname }',instr('{ MERGEFIELD
myname },',',')-1)" }

Notice that
a. all the {} need to be inserted using ctrl-F9
b. if you insert a DATABASE field from the Database toolbar (which I doubt
you will actually be able to do with d.mdb because it has no tables or
queries) there will be a long string inside \c "". You can get rid of it.
c. normally when you insert a database field from the database toolbar,
Word inserts a \h, which causes a heading row to be inserted. By leaving out
the \h and returning only a single value, the result is scalar rather than
tabular, so Word does not create a table, so the result can e anywhere in
the text flow.
d. the previous examples had a variable in the left/instr expression. In
this case the SQL is operating on the literal that Word evaluates from {
MERGEFIELD myname }, so
- you need to surround it using quotes
- you can add a comma directly at the end of the literal instead of using
+','
e. you don't need a FROM clause.

Peter Jamieson

"Mark Ainsworth" wrote in message
...
I would like to pick up just a specific word, e.g. the first word, from the
contents of a Word mail merge field.
For example, if the mail merge field contains the name in the format
"LastName, FirstName", I want to just display the lastname.

Is there a way of doing this in a mail merge field? If not, is there a way
to call a custom macro from a field so that the formatting of the field
can
be performed while the mail merge is executing?

Many thanks,
Mark