Using if fields in mail merge to ignore empty fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Larry S. - 11 Aug 2006 21:16 GMT

I am using Access 2002 to set up a mail merge letter and envelope. Some of
the records in my Access database don't have all of the name and address
fields. In older versions of Access that I used in the past, I was able to
use an "IF" field to to essentially say that if the field existed to add a
space after it, otherwise don't add the space. i.e. one record has a
"middlename" field, another doesn't. I don't want the one without the
middlename field to have two spaces between first and last name, which would
happen if I hard coded the spaces in. Same thing if a record doesn't have a
salutation field, or perhaps a title field.

How can I use if messages and/or switches to best set this up? Can it be
done both ways? If so, I would appreciate getting info on both, and the
reasons to prefer one or the other. Thanks.

Signature

Larry S

Reply to this Message

Doug Robbins - Word MVP - 12 Aug 2006 05:08 GMT

You can use either an If() function in an Access query or an
If...then...Else... field in the mail merge main document.

For the latter the construction would be

{ MERGEFIELD firstname } { IF { MERGEFIELD middleinitial } <> "" "{
MERGEFIELD middleinitial } { MERGEFIELD lastname }" "{ MERGEFIELD
lastname }" }

However, I would always manipulate the data in the database.

Signature

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

>I am using Access 2002 to set up a mail merge letter and envelope. Some of
> the records in my Access database don't have all of the name and address
[quoted text clipped - 12 lines]
> done both ways? If so, I would appreciate getting info on both, and the
> reasons to prefer one or the other. Thanks.

Reply to this Message

Larry S. - 13 Aug 2006 21:35 GMT

Hi Doug,

Thanks for your answer. How can I get two different entries as a response
to the IF statement. The way I want to use this is that if the middle name
exists, to print the middle name THEN ADD A SPACE. In other words to insert
both a field and selected text after that field. I can also see using this
to add things like commas and periods, depending on the context.

The WORD help files seem to have limited information on these kind of
issues. Is there a site that I can use as a resource that would give me full
help files on things like conditional statements, switches, etc? If so, what
would you recommend?

The idea of using the database queries to manipulate the data sounds good,
but I'm not sure just how to do that. I used to work with SQL some years
ago, but frankly have forgotten much of what I knew. Are there options in
Access that would let me do this directly, or do I need to write these
statements directly into the SQL ? In either case, and clues you can give me
as to how to do it and where to find more info for what I'm not sure of will
be helpful.

Thanks.

Signature

Larry S

> You can use either an If() function in an Access query or an
> If...then...Else... field in the mail merge main document.
[quoted text clipped - 23 lines]
> > done both ways? If so, I would appreciate getting info on both, and the
> > reasons to prefer one or the other. Thanks.

Reply to this Message

Doug Robbins - Word MVP - 14 Aug 2006 22:39 GMT

Use the field construction that I gave you. You must use Ctrl+F9 to insert
each pair of field delimiters and just change the names of the mergefields
that I used in the example to the names of the fields in your data source.
Use Alt+F9 to toggle the display of the field codes.

Signature

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

> Hi Doug,
>
[quoted text clipped - 60 lines]
>> > the
>> > reasons to prefer one or the other. Thanks.

Reply to this Message

Larry S. - 15 Aug 2006 04:48 GMT

OK, Thanks. Any suggestions on how to make the changes in the database if I
want to go that route? Would I do that in SQL?

Signature

Larry S

> Use the field construction that I gave you. You must use Ctrl+F9 to insert
> each pair of field delimiters and just change the names of the mergefields
[quoted text clipped - 65 lines]
> >> > the
> >> > reasons to prefer one or the other. Thanks.

Reply to this Message

Doug Robbins - Word MVP - 15 Aug 2006 08:10 GMT

Use a query in Access.

Signature

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

> OK, Thanks. Any suggestions on how to make the changes in the database if
> I
[quoted text clipped - 85 lines]
>> >> > the
>> >> > reasons to prefer one or the other. Thanks.

Reply to this Message

Graham Mayor - 12 Aug 2006 05:56 GMT

You can use conditional fields exactly as before.
{IF {Mergefield Fieldname} <> "" "{Mergefield Fieldname} "}
will add the field and a following space if the field has content. Otherwise
it does nothing.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> I am using Access 2002 to set up a mail merge letter and envelope.
> Some of the records in my Access database don't have all of the name
[quoted text clipped - 10 lines]
> it be done both ways? If so, I would appreciate getting info on
> both, and the reasons to prefer one or the other. Thanks.

Reply to this Message

Larry S. - 13 Aug 2006 22:07 GMT

Signature

Larry S
Graham,

Thanks for getting back to me. From the way you wrote out the "then"
clause, it looks like you've added the space by including a space between the
closing bracket of Mergefield, and the ending quote. Is this correct? Can I
add punctuation such as a comma the same way, by including it between the
bracket and the quote? Can I include more than one field by including them
both between the quotes, and possibly add a space or punctuation between them
by writing "{Mergefield Fieldname1}, {Mergefield Fieldname2}" ?

Can you refer me to an online resource that would give me this kind of
information, so I can look it up myself as needed? Also, is there a way to
add spaces with switches? If so, which would you recommend?

Thanks.

Larry

> You can use conditional fields exactly as before.
> {IF {Mergefield Fieldname} <> "" "{Mergefield Fieldname} "}
[quoted text clipped - 15 lines]
> > it be done both ways? If so, I would appreciate getting info on
> > both, and the reasons to prefer one or the other. Thanks.

Reply to this Message

Graham Mayor - 14 Aug 2006 05:46 GMT

You can add just about anything you want between the quotes including
punctuation, fields, graphics etc. You are simply telling Word to insert
that lot if the field has content. You may find
http://www.gmayor.com/formatting_word_fields.htm useful.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

>> You can use conditional fields exactly as before.
>> {IF {Mergefield Fieldname} <> "" "{Mergefield Fieldname} "}
[quoted text clipped - 23 lines]
>>> it be done both ways? If so, I would appreciate getting info on
>>> both, and the reasons to prefer one or the other. Thanks.

Reply to this Message

Larry S. - 14 Aug 2006 06:58 GMT

Hi Graham,

Thanks for your suggestions. I checked out your website, and it is
obviously a fantastic resource! For those of us with just enough computer
knowledge to be dangerous :) , It's sure great to have people like you
around.

Signature

Larry S

> You can add just about anything you want between the quotes including
> punctuation, fields, graphics etc. You are simply telling Word to insert
[quoted text clipped - 28 lines]
> >>> it be done both ways? If so, I would appreciate getting info on
> >>> both, and the reasons to prefer one or the other. Thanks.

Reply to this Message