blank when zero

[teachelr]

I have a Purchase order template that I am working on and I want to include a
calculated field for quantity * price. I have 15 lines for items on the PO,
but if I have a calculated field for quantity * price and I have nothing on
the line it still has a 0. I don't want 14 zeros when I only have 1 item on
my PO. any suggestions.

thanks is advance.

 

[Greg Maxey]

teachelr,

One way is to nest your formula in an IF field. If the formula result
(nothing times nothing, or nothing times something, or something times
nothing) returns a syntax error then display nothing, else display the
formulat result:

{IF {Your formula}="!Syntax Error, *"""{Your formula}}

 

[Suzanne S. Barnhill]

Look in Word's Help for the "numeric picture switch" topic and look
specifically for the "positive, negative, zero" format. An example of a
switch that would suppress zero here is:

#,##0.00;(#,##0.00);''

The last part is two apostrophes rather than a quotation mark (and the whole
picture should be enclosed in double quotes). I believe it also works if you
put nothing or a space after the last semicolon. I'm basing this on Google
and vague memories; perhaps someone more knowledgeable will post the right
answer!

There are a lot of variations on this answer, but here's the one I would
consider authoritative:
http://www.google.com/groups?selm=uh9TatVRBHA.1656@tkmsftngp03&output=gplain

 

[Graham Mayor]

This is covered in the http://www.gmayor.com/formatting_word_fields.htm page
on my web site. The required syntax would be


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


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

 

[Graham Mayor]

Oops - pressed the wrong key - my post should have finished
The correct syntax would be

\# "$,0.00;(,$0.00);"


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


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

 

[Greg]

Suzanne/Graham,

I don't see how your suggestions solves the OPs question.
The OP is using a calculation field in a form. I don't
see any combination of switches that will eliminate the
Zero when one or both of the variables is blank.

 

[Suzanne S. Barnhill]

These same numeric pictures are available for Number and Calculation fields
in forms. His calculated result is a zero and he doesn't want to display a
zero; this numeric picture will suppress the zero.

 

[Greg]

Suzanne,

Have you tested this? Unless you have talked to him and
have inside information, his price and quantity fields
are "blank" (his words where "nothing on the line") and
the actual calculation is a syntax error. The from
calculation field masks this with a 0.00 display using the
switch you provided. Even if I enter 0 in either of the
two variable fields or both I am still getting a display
0.00 in the calculation field using your switch.

While your switch will mask the 0 result if both variables
are 0 in a regular formula field {=({Text1}*{Text2}) \#
#,##0.00;(#,##0.00);''}, if either is blank a syntax error
is returned.

If you can't confirm this then something screwy is going
on in both my 2000 and 2003 versions.

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Greg > écrivait :
In this message, < Greg > wrote:

|| Suzanne,
||
|| Have you tested this? Unless you have talked to him and
|| have inside information, his price and quantity fields
|| are "blank" (his words where "nothing on the line") and
|| the actual calculation is a syntax error. The from
|| calculation field masks this with a 0.00 display using the
|| switch you provided. Even if I enter 0 in either of the
|| two variable fields or both I am still getting a display
|| 0.00 in the calculation field using your switch.
||
|| While your switch will mask the 0 result if both variables
|| are 0 in a regular formula field {=({Text1}*{Text2}) \#
|| #,##0.00;(#,##0.00);''}, if either is blank a syntax error
|| is returned.
||

(BTW, you are missing a double quote in your formula... Between \# and the
first #. A typo?)

I have just tested it on Word 2002.

I used a 3col x 5row table.

I put the following field in the third cell of the first row (C1):
{=({=A1}*{=B1}) \# "# ##0,00;(# ##0,00);''}

C1 is always empty if A1 or B1 are either empty or contain a zero. If both
A1 and B1 contain non-zero numbers, then the result is properly displayed.
The switch works as expected on my machine.

OTOH, I think that if you had
{=({=A1}/{=B1}) \# "# ##0,00;(# ##0,00);''}
then something like what you posted would be necessary to handle the Divide
by zero error message.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Greg Maxey]

JGM,

You are right of course wrt the typo.

My original post was dealing with formfield bookmark names vice table cell
references .

{IF {Your formula}="!Syntax Error, *"""{Your formula}} meaning
{IF {=({Text1}*{Text2})}="!Syntax Error, *"""{=({Text1}*{Text2})}}

As the the user posted in the Table group he can obviosly use table cell
references and your formula is correct.

Still, I am unable to make Suzanne's switch or any other switch return a
"blank" in a formfield calculation type field if either of the variables or
if both varable is blank.

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Greg Maxey > écrivait :
In this message, < Greg Maxey > wrote:

|| JGM,
||
|| You are right of course wrt the typo.
||
|| My original post was dealing with formfield bookmark names vice table
cell
|| references .
||
|| {IF {Your formula}="!Syntax Error, *"""{Your formula}} meaning
|| {IF {=({Text1}*{Text2})}="!Syntax Error, *"""{=({Text1}*{Text2})}}
||
|| As the the user posted in the Table group he can obviosly use table cell
|| references and your formula is correct.
||
|| Still, I am unable to make Suzanne's switch or any other switch return a
|| "blank" in a formfield calculation type field if either of the variables
or
|| if both varable is blank.

Have you tried:
{=({=Text1}*{=Text2}) \# "# ##0,00;(# ##0,00);''}
??

The switch works really well over here...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Greg Maxey]

JGM,

You are right again for application in a standard formula field. The point
that I am trying to express and fairly miserably is ... I can't make the
numeric picture switch that Suzanne or you are suggesing work to mask a 0 in
formfield "calculation" type field. I don't think it will work, but as I
have been wrong before I can certainly be wrong again.

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Greg Maxey > écrivait :
In this message, < Greg Maxey > wrote:

|| JGM,
||
|| You are right again for application in a standard formula field. The
point
|| that I am trying to express and fairly miserably is ... I can't make the
|| numeric picture switch that Suzanne or you are suggesing work to mask a 0
in
|| formfield "calculation" type field. I don't think it will work, but as I
|| have been wrong before I can certainly be wrong again.
||

Ah!

Yes, it is possible, but you have to cheat a bit.

Insert your formfield and double click on it to access its properties.
Set it to Calculation, insert the formula you want [e.g. =(A1*B1)] and leave
the number format list blank. Hit OK

Now, do ALT-F9 to display the field codes. You should have something like:
{ FORMTEXT { =(A1*B1) }}
Now, add the switch to obtain:
{ FORMTEXT { =(A1*B1) }\# "#,##0.00;(#,##0.00);" }
Do ALT-F9 again.

Protect the form, now it should work.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Greg Maxey]

JGM,

Once again a session with you is better than school of the boat .
Thanks.

--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

Bonjour,

Dans son message, < Greg Maxey > écrivait :

Ah!

Yes, it is possible, but you have to cheat a bit.

Insert your formfield and double click on it to access its properties.
Set it to Calculation, insert the formula you want [e.g. =(A1*B1)]
and leave the number format list blank. Hit OK

Now, do ALT-F9 to display the field codes. You should have something
like: { FORMTEXT { =(A1*B1) }}
Now, add the switch to obtain:
{ FORMTEXT { =(A1*B1) }\# "#,##0.00;(#,##0.00);" }
Do ALT-F9 again.

Protect the form, now it should work.

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Greg Maxey > écrivait :
In this message, < Greg Maxey > wrote:

|| JGM,
||
|| Once again a session with you is better than school of the boat .
|| Thanks.
||

LOL
What a memory you have!

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[macropod]

Hi Guys,

Why 'cheat' when you can use:
{={IF Text1 <> 0 Text1 0}*{IF Text2 <> 0 Text2 0} \# $,0.00;$(,0.00);}

This has the distinct advantage of not being held hostage to a table
structure.

Cheers

Bonjour,

Dans son message, < Greg Maxey > écrivait :
In this message, < Greg Maxey > wrote:

|| JGM,
||
|| You are right again for application in a standard formula field. The
point
|| that I am trying to express and fairly miserably is ... I can't make the
|| numeric picture switch that Suzanne or you are suggesing work to mask a 0
in
|| formfield "calculation" type field. I don't think it will work, but as I
|| have been wrong before I can certainly be wrong again.
||

Ah!

Yes, it is possible, but you have to cheat a bit.

Insert your formfield and double click on it to access its properties.
Set it to Calculation, insert the formula you want [e.g. =(A1*B1)] and leave
the number format list blank. Hit OK

Now, do ALT-F9 to display the field codes. You should have something like:
{ FORMTEXT { =(A1*B1) }}
Now, add the switch to obtain:
{ FORMTEXT { =(A1*B1) }\# "#,##0.00;(#,##0.00);" }
Do ALT-F9 again.

Protect the form, now it should work.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < macropod > écrivait :
In this message, < macropod > wrote:

|| Hi Guys,
||
|| Why 'cheat' when you can use:
|| {={IF Text1 <> 0 Text1 0}*{IF Text2 <> 0 Text2 0} \# $,0.00;$(,0.00);}
||
|| This has the distinct advantage of not being held hostage to a table
|| structure.
||

Greg was inquiring about numerical switches in Calculated Formfields. The
problem was not in the calculation, but in displaying the result.

Are you saying there is a way to make such a field hide results that are
equal to 0 through the field property dialog?
I could not get the last " ; " (the one used for formatting 0 results) to
stick through the dialog. That is why I mentioned "Cheating" as it was the
only way I found to do it easily and quickly.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[macropod]

Hi Jean-Guy Marcil,

I don't believe there is any way to hide the empty Form field itself. If you
leave it empty you get the default with 5 spaces. You could reduce that to
one space by simply hitting the space bar, but that would pointless unless
you're trying to affect the spacing of characters around the Form field,
since it doesn't print anyway.

The equation field that I posted:
{={IF Text1 <> 0 Text1 0}*{IF Text2 <> 0 Text2 0} \# $,0.00;$(,0.00);}
affects only the display of the calculated result, and avoids the 'Syntax
error' message that you get with:
{={Text1}*{Text2}}
if either Form field is left empty.

Cheers

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < macropod > écrivait :
In this message, < macropod > wrote:

|| Hi Jean-Guy Marcil,
||
|| I don't believe there is any way to hide the empty Form field itself. If
you
|| leave it empty you get the default with 5 spaces. You could reduce that
to
|| one space by simply hitting the space bar, but that would pointless
unless
|| you're trying to affect the spacing of characters around the Form field,
|| since it doesn't print anyway.

True, but this is not what we are talking about!
A calculated formfield cannot be modified directly by the user. It shows a
result based on calculations.

If the said result = 0, it will display 0 (And I believe it will also
display 0 if the result of the calculation is actually "empty/nil", as it is
the case when you have a calculated formfield that calculate the product of
two cells, but the cells are actually empty, which is not the same as
containing a zero). With regular equation fields, you can add the list
separator at the end of the numeric format switch. If the list separator is
followed by nothing at all, this means that Word will display nothing if the
result of the equation is 0.
Now, in a calculated formfield, you have a dropdown list of numeric format
to choose from in the property dialog box. You can also create your own by
just typing it in the dropdown field. This is where I could not get the list
separator added at the end of the format switch to stick. Word would keep
the list separator as part of the format switch but totally ignore it if a
calculated result was = 0. Hence my suggestion to Greg to cheat a bit by
creating the calculated formfield, and then modifying the field code instead
of trying to achieve the goal of hiding 0 result through the formfield
property dialog. This actually work (Well it worked on Word 2002 on my
machine!). The formfield will be hidden if the result is 0 or "nil"

Does that make any sense now?
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[macropod]

Hi Jean-Guy Marcil,

While you may not be able to hide a calculated Form Field, why would you use
one when a simple formula field can return the desired result with a lot
less fiddling? What I showed is how to use a standard formula field to take
the output from two Form Fields and give a 'correct' result when either or
both Form Fields is empty.

Cheers