Zero Values

[Sue]

This was a previous post of mine. I came back to this issue and want to make
sure that my post is seen. I'm trying to hide zero values in a form field
calculation.

I've done what was suggested below, and the $0.00 display is gone now, but
now
it's not calculating the fields. This is what I've used:
{=Trans1+Hotel1+Meals1+Car1+Misc1 \# $,0.00;-$,0.00;}
Do I have to format the fields used in the calculation any certain way?
Right now they
are formatted as 0.00.

Thanks!

 

[Tony Jollans]

You do have them all set to Calculate on Exit, don't you?

 

[Sue]

Yes I do. I just don't get it. There has to be something simple I'm not
doing here. The calculation works fine until I put in the format switch to
not display Zero values, then nothing calculates. My calculation is
{=Trans1+Hotel1+Meals1+Car1+Misc1 \# $,0.00;-$,0.00;}. If I do a simple
=Trans1+Hotel1+Meals1+Car1+Misc1 it works, but displays $0.00 when there's no
data.

 

[Tony Jollans]

I just set up a form with 5 formfields with the same names as yours and then
added a calculated field and cut and pasted the field code from your post
and it all worked fine for me, so I'm not sure what may be the problem.

However, I don't get $0.00 without the switch - just plain zero - just
realised what's probably wrong. Do you have a format set via the formfield
options dialog as well as the format switch - if so, remove it.

 

[Sue]

It WORKS! Thank you SO MUCH Tony! This was driving me up a wall! I
actually have 3 forms to apply this to now. I'm thrilled. Thanks again!

 

[Jean-Guy Marcil]

Sue was telling us:
Sue nous racontait que :

Yes I do. I just don't get it. There has to be something simple I'm
not doing here. The calculation works fine until I put in the format
switch to not display Zero values, then nothing calculates. My
calculation is {=Trans1+Hotel1+Meals1+Car1+Misc1 \# $,0.00;-$,0.00;}.
If I do a simple =Trans1+Hotel1+Meals1+Car1+Misc1 it works, but
displays $0.00 when there's no data.

{=Trans1+Hotel1+Meals1+Car1+Misc1 \# $,0.00;-$,0.00;}

Why the "," between the ? and the 0?
Make sure there is a space after the last ";".


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

 

[Tony Jollans]

Hi Jean-Guy,

The comma makes, for example 1234 display as $1,234.00 instead of just
$1234.00 (and I have never found spaces necessary in fields except as
delimiters).

 

[Tony Jollans]

Glad you're sorted.

I have never found anything official about this but it appears that using
any format in the dialog (other than those lsited in the dropdown, anyway)
is a bit unpredictable, and especially so if combined with a format on the
formtext field.

What I think may happen is that Word attempts to apply the second format to
the results of the first format but fails as it is not numeric any more.

 

[Jean-Guy Marcil]

Tony Jollans was telling us:
Tony Jollans nous racontait que :

Hi Jean-Guy,

The comma makes, for example 1234 display as $1,234.00 instead of just
$1234.00 (and I have never found spaces necessary in fields except as
delimiters).

Oh, yeah, forgot about the thousand separator.... On my machine it is the
non-breaking space (I work with French language customers).

As for the space, I have found that very often (depending on the field and
the switch you add), if you do not have a space between the end of the
switch and the closing }in a field, then you get a Syntax error message. In
this case, this is what I get, it may be my machine that is quirky!


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