Using if fields in mail merge to ignore empty fields

[Larry S.]

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.

 

[Doug Robbins - Word MVP]

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.

--
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

 

[Graham Mayor]

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.

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Larry S.]

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.

 

[Larry S.]

--
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

 

[Graham Mayor]

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.

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Larry S.]

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.

 

[Doug Robbins - Word MVP]

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.

--
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

 

[Larry S.]

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?

 

[Doug Robbins - Word MVP]

Use a query in Access.

--
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