[ew68]

I am trying to create an address book using mail merge with the layout being
formatted for labels. The input file is an Access Database.

After the mail merge is performed, I have to manually delete all of the "-"
characters. These are for Access database fields which are not populated. I
guess this is some kind of null value.

Is there some way for me to automatically remove the null fields from the
label? In the label, I have some entries which look like "cell -" which
means that no value was provided for the "cell" field in the Access database
for the given record. I would like the row of the label containing "cell - "
to be removed/deleted.

Any suggestions on how I can do this in an automated fashion? Deleting
these kind of rows manually is very tedious.

[Doug Robbins - Word MVP]

Why use mail merge when Access has its own label wizard?

To deal with the blank fields issue, use the fact that in Access
Something+Null = Null

You can just use the one control on the label with something like

=[F_Name] & " " & [L_Name] & (Chr(13)+Chr(10)+"Cell: "+[Cell])

as the data for the control.

With that control, if there was data in the Cell field, there would be a
second line on the label, commencing with Cell: and the cell number; If the
Cell field was empty, there would only the one line on the label with the
first and the last name.

To do it with mailmerge, you have to use an If...then...Else field
construction
{ IF { MERGEFIELD Cell } "" "Cell: { MERGEFIELD Cell } }

It becomes quite involved however if there are multiple fields that might be
empty and you do not want the words such as Cell: or blank lines appearing
in the output.


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

"ew68" wrote in message
...
I am trying to create an address book using mail merge with the layout
being
formatted for labels. The input file is an Access Database.

After the mail merge is performed, I have to manually delete all of the
"-"
characters. These are for Access database fields which are not populated.
I
guess this is some kind of null value.

Is there some way for me to automatically remove the null fields from the
label? In the label, I have some entries which look like "cell -" which
means that no value was provided for the "cell" field in the Access
database
for the given record. I would like the row of the label containing
"cell - "
to be removed/deleted.

Any suggestions on how I can do this in an automated fashion? Deleting
these kind of rows manually is very tedious.

[Doug Robbins - Word MVP]

Why use mail merge when Access has its own label wizard?

To deal with the blank fields issue, use the fact that in Access
Something+Null = Null

You can just use the one control on the label with something like

=[F_Name] & " " & [L_Name] & (Chr(13)+Chr(10)+"Cell: "+[Cell])

as the data for the control.

With that control, if there was data in the Cell field, there would be a
second line on the label, commencing with Cell: and the cell number; If the
Cell field was empty, there would only the one line on the label with the
first and the last name.

To do it with mailmerge, you have to use an If...then...Else field
construction
{ IF { MERGEFIELD Cell } "" "Cell: { MERGEFIELD Cell } }

It becomes quite involved however if there are multiple fields that might be
empty and you do not want the words such as Cell: or blank lines appearing
in the output.


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

"ew68" wrote in message
...
I am trying to create an address book using mail merge with the layout
being
formatted for labels. The input file is an Access Database.

After the mail merge is performed, I have to manually delete all of the
"-"
characters. These are for Access database fields which are not populated.
I
guess this is some kind of null value.

Is there some way for me to automatically remove the null fields from the
label? In the label, I have some entries which look like "cell -" which
means that no value was provided for the "cell" field in the Access
database
for the given record. I would like the row of the label containing
"cell - "
to be removed/deleted.

Any suggestions on how I can do this in an automated fashion? Deleting
these kind of rows manually is very tedious.

[macropod[_2_]]

Hi ew68,

In Word:
.. duplicate the mailmerge field to be suppressed (eg «MyData») so that you get «MyData»«MyData»;
.. select both fields and Press Ctrl-F9 to enclose them in a new field, thus { «MyData»«MyData» };
.. fill in between the braces so that you end up with {IF«myData» "cell - " "«MyData»¶
"}, where the '¶' is a paragraph mark or line-feed, depending on which you're using;
.. delete the existing paragraph mark or line-feed after the field;
.. position the cursor anywhere in this field and press F9 to update it;
.. since you're working with labels, complete the coding for the first label, then simply copy & paste it to each of the others.
.. run your mailmerge.

If you find that you've sometimes got 'Cell' and sometimes 'cell' (or any other uppercase/lowercase mix, reselect the above fields
and press F9 to expose the field code. You'll now see something like:
{IF{MERGEFIELD myData} "cell - " "{ MERGEFIELD myData}¶
"}
Simply edit the code so that you end up with:
{IF{MERGEFIELD myData \* lower} "cell - " "{ MERGEFIELD myData}¶
"}

--
Cheers
macropod
[Microsoft MVP - Word]


"ew68" wrote in message ...
I am trying to create an address book using mail merge with the layout being
formatted for labels. The input file is an Access Database.

After the mail merge is performed, I have to manually delete all of the "-"
characters. These are for Access database fields which are not populated. I
guess this is some kind of null value.

Is there some way for me to automatically remove the null fields from the
label? In the label, I have some entries which look like "cell -" which
means that no value was provided for the "cell" field in the Access database
for the given record. I would like the row of the label containing "cell - "
to be removed/deleted.

Any suggestions on how I can do this in an automated fashion? Deleting
these kind of rows manually is very tedious.

[macropod[_2_]]

Hi ew68,

In Word:
.. duplicate the mailmerge field to be suppressed (eg «MyData») so that you get «MyData»«MyData»;
.. select both fields and Press Ctrl-F9 to enclose them in a new field, thus { «MyData»«MyData» };
.. fill in between the braces so that you end up with {IF«myData» "cell - " "«MyData»¶
"}, where the '¶' is a paragraph mark or line-feed, depending on which you're using;
.. delete the existing paragraph mark or line-feed after the field;
.. position the cursor anywhere in this field and press F9 to update it;
.. since you're working with labels, complete the coding for the first label, then simply copy & paste it to each of the others.
.. run your mailmerge.

If you find that you've sometimes got 'Cell' and sometimes 'cell' (or any other uppercase/lowercase mix, reselect the above fields
and press F9 to expose the field code. You'll now see something like:
{IF{MERGEFIELD myData} "cell - " "{ MERGEFIELD myData}¶
"}
Simply edit the code so that you end up with:
{IF{MERGEFIELD myData \* lower} "cell - " "{ MERGEFIELD myData}¶
"}

--
Cheers
macropod
[Microsoft MVP - Word]


"ew68" wrote in message ...
I am trying to create an address book using mail merge with the layout being
formatted for labels. The input file is an Access Database.

After the mail merge is performed, I have to manually delete all of the "-"
characters. These are for Access database fields which are not populated. I
guess this is some kind of null value.

Is there some way for me to automatically remove the null fields from the
label? In the label, I have some entries which look like "cell -" which
means that no value was provided for the "cell" field in the Access database
for the given record. I would like the row of the label containing "cell - "
to be removed/deleted.

Any suggestions on how I can do this in an automated fashion? Deleting
these kind of rows manually is very tedious.