Mergefield suppress if data field blank

  • Thread starter JB reluctantly from WP
  • Start date
J

JB reluctantly from WP

Hello,

I have an Excel database file, and am merging into a Word document. I am
familiar with Word's merge process. However, I have a field in the database
called RADIO that is a 3-digit number, or the field may be blank. The Word
document displays the blank fields as a zero. I want them to be blank. I
have read previous posts about using switches within my mergefield, and they
kind of make sense, but none seem to be the right fix for my particular need.

Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 
C

Cindy B

I’ve seen a lot of inconsistencies with newer versions of word as well. I
would suggest creating an If Then Else statement to control the field. “If
mergefield is not blank then insert otherwise do nothingâ€

Good luck.
 
M

macropod

Hi JB,

You can fix this by adding a numeric picture switch to the field. To do
this:
.. select your mergefield and press Shift-F9 to expose the code. It should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers
 
J

JB reluctantly from WP

Macropod,

Thank you so much! I knew I didn't have to make long complicated IF, THEN,
ELSE statements to get the result I wanted! I really appreciate your help.

~Julie

macropod said:
Hi JB,

You can fix this by adding a numeric picture switch to the field. To do
this:
.. select your mergefield and press Shift-F9 to expose the code. It should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers

--
macropod
[MVP - Microsoft Word]


JB reluctantly from WP said:
Hello,

I have an Excel database file, and am merging into a Word document. I am
familiar with Word's merge process. However, I have a field in the database
called RADIO that is a 3-digit number, or the field may be blank. The Word
document displays the blank fields as a zero. I want them to be blank. I
have read previous posts about using switches within my mergefield, and they
kind of make sense, but none seem to be the right fix for my particular need.

Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 
M

malycom

Macprod, this has helped me a little also, but if you are still viewing this
2 years on, I wonder if you cousl assist a little more?

I have also used your {MERGEFIELD ExcelData \# 0;;} example in fields but
there are instances where there are problems.

If an address is '43 something street', the result coming back is '43' with
the rest of the line missing.

This seems to happen everywhere there is more than one word in a mergefield.
Any time there is a space, the data inserted stops at the first space.

Is there anything you can suggest?

Regards

macropod said:
Hi JB,

You can fix this by adding a numeric picture switch to the field. To do
this:
.. select your mergefield and press Shift-F9 to expose the code. It should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers

--
macropod
[MVP - Microsoft Word]


JB reluctantly from WP said:
Hello,

I have an Excel database file, and am merging into a Word document. I am
familiar with Word's merge process. However, I have a field in the database
called RADIO that is a 3-digit number, or the field may be blank. The Word
document displays the blank fields as a zero. I want them to be blank. I
have read previous posts about using switches within my mergefield, and they
kind of make sense, but none seem to be the right fix for my particular need.

Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 
M

macropod

Hi malycom,

If you want an alpha-numeric string (eg an address) to appear in full, delete the picture switch - it's only intended to suppress 0s
in empty numeric fields. Using it elsewhere returns just the number - as you've discovered.

--
Cheers
macropod
[MVP - Microsoft Word]


malycom said:
Macprod, this has helped me a little also, but if you are still viewing this
2 years on, I wonder if you cousl assist a little more?

I have also used your {MERGEFIELD ExcelData \# 0;;} example in fields but
there are instances where there are problems.

If an address is '43 something street', the result coming back is '43' with
the rest of the line missing.

This seems to happen everywhere there is more than one word in a mergefield.
Any time there is a space, the data inserted stops at the first space.

Is there anything you can suggest?

Regards

macropod said:
Hi JB,

You can fix this by adding a numeric picture switch to the field. To do
this:
.. select your mergefield and press Shift-F9 to expose the code. It should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers

--
macropod
[MVP - Microsoft Word]


JB reluctantly from WP said:
Hello,

I have an Excel database file, and am merging into a Word document. I am
familiar with Word's merge process. However, I have a field in the database
called RADIO that is a 3-digit number, or the field may be blank. The Word
document displays the blank fields as a zero. I want them to be blank. I
have read previous posts about using switches within my mergefield, and they
kind of make sense, but none seem to be the right fix for my particular need.

Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 
M

malycom

Thanks for your reply Macprod - Unfortunately, I have no idea what a picture
switch is. I think I have enough to be able to do what I need for now.

Thanks for your reply though

Regards

macropod said:
Hi malycom,

If you want an alpha-numeric string (eg an address) to appear in full, delete the picture switch - it's only intended to suppress 0s
in empty numeric fields. Using it elsewhere returns just the number - as you've discovered.

--
Cheers
macropod
[MVP - Microsoft Word]


malycom said:
Macprod, this has helped me a little also, but if you are still viewing this
2 years on, I wonder if you cousl assist a little more?

I have also used your {MERGEFIELD ExcelData \# 0;;} example in fields but
there are instances where there are problems.

If an address is '43 something street', the result coming back is '43' with
the rest of the line missing.

This seems to happen everywhere there is more than one word in a mergefield.
Any time there is a space, the data inserted stops at the first space.

Is there anything you can suggest?

Regards

macropod said:
Hi JB,

You can fix this by adding a numeric picture switch to the field. To do
this:
.. select your mergefield and press Shift-F9 to expose the code. It should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers

--
macropod
[MVP - Microsoft Word]


"JB reluctantly from WP" <[email protected]>
wrote in message Hello,

I have an Excel database file, and am merging into a Word document. I am
familiar with Word's merge process. However, I have a field in the
database
called RADIO that is a 3-digit number, or the field may be blank. The
Word
document displays the blank fields as a zero. I want them to be blank. I
have read previous posts about using switches within my mergefield, and
they
kind of make sense, but none seem to be the right fix for my particular
need.

Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 
M

macropod

Hi malycom,

The picture switch in this case is '\# 0;;' - and is referred to as such in my old post that you included in your initial post.

Simply delete it and run your mailmerge.

--
Cheers
macropod
[MVP - Microsoft Word]


malycom said:
Thanks for your reply Macprod - Unfortunately, I have no idea what a picture
switch is. I think I have enough to be able to do what I need for now.

Thanks for your reply though

Regards

macropod said:
Hi malycom,

If you want an alpha-numeric string (eg an address) to appear in full, delete the picture switch - it's only intended to suppress
0s
in empty numeric fields. Using it elsewhere returns just the number - as you've discovered.

--
Cheers
macropod
[MVP - Microsoft Word]


malycom said:
Macprod, this has helped me a little also, but if you are still viewing this
2 years on, I wonder if you cousl assist a little more?

I have also used your {MERGEFIELD ExcelData \# 0;;} example in fields but
there are instances where there are problems.

If an address is '43 something street', the result coming back is '43' with
the rest of the line missing.

This seems to happen everywhere there is more than one word in a mergefield.
Any time there is a space, the data inserted stops at the first space.

Is there anything you can suggest?

Regards

:

Hi JB,

You can fix this by adding a numeric picture switch to the field. To do
this:
.. select your mergefield and press Shift-F9 to expose the code. It should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers

--
macropod
[MVP - Microsoft Word]


"JB reluctantly from WP" <[email protected]>
wrote in message Hello,

I have an Excel database file, and am merging into a Word document. I am
familiar with Word's merge process. However, I have a field in the
database
called RADIO that is a 3-digit number, or the field may be blank. The
Word
document displays the blank fields as a zero. I want them to be blank. I
have read previous posts about using switches within my mergefield, and
they
kind of make sense, but none seem to be the right fix for my particular
need.

Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 
P

Peter Jamieson

The "picture switch" is the "\# 0;;" part of the field which specifies a
format for your data.

In this case "\#" is the switch and "0;;" is the picture.

"switch" and "picture" are both longstanding computer jargon - "switch"
referring to something that alters the behaviour of a "command" of some
kind. In this case the "command" is a field code. "picture" because a
format such as "0.00" is in a sense a "picture" of how the data should look.


Peter Jamieson

http://tips.pjmsn.me.uk
Thanks for your reply Macprod - Unfortunately, I have no idea what a picture
switch is. I think I have enough to be able to do what I need for now.

Thanks for your reply though

Regards

macropod said:
Hi malycom,

If you want an alpha-numeric string (eg an address) to appear in full, delete the picture switch - it's only intended to suppress 0s
in empty numeric fields. Using it elsewhere returns just the number - as you've discovered.

--
Cheers
macropod
[MVP - Microsoft Word]


malycom said:
Macprod, this has helped me a little also, but if you are still viewing this
2 years on, I wonder if you cousl assist a little more?

I have also used your {MERGEFIELD ExcelData \# 0;;} example in fields but
there are instances where there are problems.

If an address is '43 something street', the result coming back is '43' with
the rest of the line missing.

This seems to happen everywhere there is more than one word in a mergefield.
Any time there is a space, the data inserted stops at the first space.

Is there anything you can suggest?

Regards

:

Hi JB,

You can fix this by adding a numeric picture switch to the field. To do
this:
.. select your mergefield and press Shift-F9 to expose the code. It should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers

--
macropod
[MVP - Microsoft Word]


"JB reluctantly from WP" <[email protected]>
wrote in message Hello,

I have an Excel database file, and am merging into a Word document. I am
familiar with Word's merge process. However, I have a field in the
database
called RADIO that is a 3-digit number, or the field may be blank. The
Word
document displays the blank fields as a zero. I want them to be blank. I
have read previous posts about using switches within my mergefield, and
they
kind of make sense, but none seem to be the right fix for my particular
need.
Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 
M

malycom

Thanks Peter - Really useful info there. That makes a lot of sense now.

Regards

Peter Jamieson said:
The "picture switch" is the "\# 0;;" part of the field which specifies a
format for your data.

In this case "\#" is the switch and "0;;" is the picture.

"switch" and "picture" are both longstanding computer jargon - "switch"
referring to something that alters the behaviour of a "command" of some
kind. In this case the "command" is a field code. "picture" because a
format such as "0.00" is in a sense a "picture" of how the data should look.


Peter Jamieson

http://tips.pjmsn.me.uk
Thanks for your reply Macprod - Unfortunately, I have no idea what a picture
switch is. I think I have enough to be able to do what I need for now.

Thanks for your reply though

Regards

macropod said:
Hi malycom,

If you want an alpha-numeric string (eg an address) to appear in full, delete the picture switch - it's only intended to suppress 0s
in empty numeric fields. Using it elsewhere returns just the number - as you've discovered.

--
Cheers
macropod
[MVP - Microsoft Word]


Macprod, this has helped me a little also, but if you are still viewing this
2 years on, I wonder if you cousl assist a little more?

I have also used your {MERGEFIELD ExcelData \# 0;;} example in fields but
there are instances where there are problems.

If an address is '43 something street', the result coming back is '43' with
the rest of the line missing.

This seems to happen everywhere there is more than one word in a mergefield.
Any time there is a space, the data inserted stops at the first space.

Is there anything you can suggest?

Regards

:

Hi JB,

You can fix this by adding a numeric picture switch to the field. To do
this:
.. select your mergefield and press Shift-F9 to expose the code. It should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers

--
macropod
[MVP - Microsoft Word]


"JB reluctantly from WP" <[email protected]>
wrote in message Hello,

I have an Excel database file, and am merging into a Word document. I am
familiar with Word's merge process. However, I have a field in the
database
called RADIO that is a 3-digit number, or the field may be blank. The
Word
document displays the blank fields as a zero. I want them to be blank. I
have read previous posts about using switches within my mergefield, and
they
kind of make sense, but none seem to be the right fix for my particular
need.
Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 
N

njt

I have a similar problem as JB except my mail merge can result in a number
such as $5,000.00 or it may be $0.00 or it may be blank. I used the example
you gave JB (i.e., {MERGEFIELD ExcelData \# 0;;}'), and it works great for my
blanks and my numbers such as $5,000 but if the value is $0.00 it shows as a
blank after I mail merge. How can I get my $0.00 values to show as zeros,
but my blanks to show as blanks.

Thanks so much for your time!

njt


macropod said:
Hi JB,

You can fix this by adding a numeric picture switch to the field. To do
this:
.. select your mergefield and press Shift-F9 to expose the code. It should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers

--
macropod
[MVP - Microsoft Word]


JB reluctantly from WP said:
Hello,

I have an Excel database file, and am merging into a Word document. I am
familiar with Word's merge process. However, I have a field in the database
called RADIO that is a 3-digit number, or the field may be blank. The Word
document displays the blank fields as a zero. I want them to be blank. I
have read previous posts about using switches within my mergefield, and they
kind of make sense, but none seem to be the right fix for my particular need.

Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 
D

Doug Robbins - Word MVP on news.microsoft.com

Use

{MERGEFIELD ExcelData \# "$,0.00;($,0.00);" }

or if you prefer

{MERGEFIELD ExcelData \# "$,0.00;-$,0.00;" }

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

njt said:
I have a similar problem as JB except my mail merge can result in a number
such as $5,000.00 or it may be $0.00 or it may be blank. I used the
example
you gave JB (i.e., {MERGEFIELD ExcelData \# 0;;}'), and it works great for
my
blanks and my numbers such as $5,000 but if the value is $0.00 it shows as
a
blank after I mail merge. How can I get my $0.00 values to show as zeros,
but my blanks to show as blanks.

Thanks so much for your time!

njt


macropod said:
Hi JB,

You can fix this by adding a numeric picture switch to the field. To do
this:
.. select your mergefield and press Shift-F9 to expose the code. It
should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so
that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers

--
macropod
[MVP - Microsoft Word]


"JB reluctantly from WP" <[email protected]>
wrote in message
Hello,

I have an Excel database file, and am merging into a Word document. I
am
familiar with Word's merge process. However, I have a field in the database
called RADIO that is a 3-digit number, or the field may be blank. The Word
document displays the blank fields as a zero. I want them to be blank.
I
have read previous posts about using switches within my mergefield, and they
kind of make sense, but none seem to be the right fix for my particular need.

Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 
N

njt

Doug,

Thanks for your response. I tried your suggestion but in my word report I
am still just getting numbers in excess of $0 (ex. $5,000) or a blank. If
the number in the Access column is entered as a $0 it shows up in my word
document as a blank. I need to make a distinction between the access entries
where we have entered $0 and entries where we have intentionally left a
blank. Any suggestions?

Thanks, Jaree

Doug Robbins - Word MVP on news.microsof said:
Use

{MERGEFIELD ExcelData \# "$,0.00;($,0.00);" }

or if you prefer

{MERGEFIELD ExcelData \# "$,0.00;-$,0.00;" }

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

njt said:
I have a similar problem as JB except my mail merge can result in a number
such as $5,000.00 or it may be $0.00 or it may be blank. I used the
example
you gave JB (i.e., {MERGEFIELD ExcelData \# 0;;}'), and it works great for
my
blanks and my numbers such as $5,000 but if the value is $0.00 it shows as
a
blank after I mail merge. How can I get my $0.00 values to show as zeros,
but my blanks to show as blanks.

Thanks so much for your time!

njt


macropod said:
Hi JB,

You can fix this by adding a numeric picture switch to the field. To do
this:
.. select your mergefield and press Shift-F9 to expose the code. It
should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so
that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers

--
macropod
[MVP - Microsoft Word]


"JB reluctantly from WP" <[email protected]>
wrote in message
Hello,

I have an Excel database file, and am merging into a Word document. I
am
familiar with Word's merge process. However, I have a field in the
database
called RADIO that is a 3-digit number, or the field may be blank. The
Word
document displays the blank fields as a zero. I want them to be blank.
I
have read previous posts about using switches within my mergefield, and
they
kind of make sense, but none seem to be the right fix for my particular
need.

Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 
D

Doug Robbins - Word MVP on news.microsoft.com

You are going to need to use an If...then...Else field construction

{ IF { MERGEFIELD ExcelData } = 0 "$0.00" {MERGEFIELD ExcelData \#
"$,0.00;($,0.00);" }}

or maybe

{ IF { MERGEFIELD ExcelData } <> "" {IF { MERGEFIELD ExcelData } > 0
{MERGEFIELD ExcelData \# "$,0.00;($,0.00);" } "$0.00" } "" }

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

njt said:
Doug,

Thanks for your response. I tried your suggestion but in my word report I
am still just getting numbers in excess of $0 (ex. $5,000) or a blank. If
the number in the Access column is entered as a $0 it shows up in my word
document as a blank. I need to make a distinction between the access
entries
where we have entered $0 and entries where we have intentionally left a
blank. Any suggestions?

Thanks, Jaree

Doug Robbins - Word MVP on news.microsof said:
Use

{MERGEFIELD ExcelData \# "$,0.00;($,0.00);" }

or if you prefer

{MERGEFIELD ExcelData \# "$,0.00;-$,0.00;" }

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

njt said:
I have a similar problem as JB except my mail merge can result in a
number
such as $5,000.00 or it may be $0.00 or it may be blank. I used the
example
you gave JB (i.e., {MERGEFIELD ExcelData \# 0;;}'), and it works great
for
my
blanks and my numbers such as $5,000 but if the value is $0.00 it shows
as
a
blank after I mail merge. How can I get my $0.00 values to show as
zeros,
but my blanks to show as blanks.

Thanks so much for your time!

njt


:

Hi JB,

You can fix this by adding a numeric picture switch to the field. To
do
this:
.. select your mergefield and press Shift-F9 to expose the code. It
should
look something like:
{MERGEFIELD ExcelData}
where 'ExcelData' is you field name
.. delete anything after 'ExcelData' and add '\# 0;;') to the field so
that
you've got:
'{MERGEFIELD ExcelData \# 0;;}'
.. press F9 to update the field
.. run your mailmerge

Cheers

--
macropod
[MVP - Microsoft Word]


"JB reluctantly from WP"
<[email protected]>
wrote in message
Hello,

I have an Excel database file, and am merging into a Word document.
I
am
familiar with Word's merge process. However, I have a field in the
database
called RADIO that is a 3-digit number, or the field may be blank.
The
Word
document displays the blank fields as a zero. I want them to be
blank.
I
have read previous posts about using switches within my mergefield,
and
they
kind of make sense, but none seem to be the right fix for my
particular
need.

Currently displayed in Word document: {Mergefield "Radio"}
I need this to only display text if not blank in database.

Any assistance is greatly appreciated!

Julie ~ reluctantly switching from WordPerfect to Word (sheesh!)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top