text field not hiding zero values - please help

[Guest]

OK, I'm puzzled with this, I can not find any help on Internet. Can not
believe nobody had this problem before. Here it is in brief - I have text
field on detail section of a report which is bound to a numeric field
"Quantity". I want this field to hide value if it's zero, so it's custom
format is something like this:
#,##0.00;-#,##0.00;#;'Null'
This is fine, but only when decimal places property for field is 0 or 2
If number of decimal places is changed to 1 or 3 and more, field that should
show nothing will now display something like .0 or .000
What's more, it gets misaligned, but this is less of a problem.
I'm using Access 97, do not know if the same behaviour is with later
versions of Access. Please let me know if you need more details on how to
reproduce the misbehaviour, but it should be easy. Simply set the format to
something like above, set decimal places property to 1 and display numeric
values from some table on the report (numeric values that equal to zero will
be shown as .0 instead of being not shown at all). Any help appreciated.
Miroslav

 

[John Spencer]

Try formatting like

#,##0.00;-#,##0.00;"";""


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

John, appreciated, but this does not change anything. My problem is not what
format would nicely hide zero values, the one I posted would work as good as
the one you suggested. Problem is that NEITHER of them will work when
DecimalPlaces property is set to 1 or 3 and more. Did you try it? I tried all
the possible options available (from setting format from code or directly,
changing decimal place property directly or in VB, before setting the format
or after, regardless). It will always show .0 instead of nothing if
decimalplaces property is not 0 or 2!

Sincerely,
Miroslav

 

[John Spencer]

In a query in Access 2000, the formatting worked. Interesting side effect
was that if the value was zero for the displayed portion (3 decimals) then
the value was hidden.

If I have the value 0.0006 with decimal places set to 3, then .001 is shown
in the results. However 0.0004 will show as blank

It works for me in a report also.

Also Tested in Access 2003


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks John, this must be an Acc97 "feature" then. I will see if I can
reproduce the problem in later versions of Access. Will revert with findings.

 

[Guest]

John, OK, there seem to be (as always) combination of problems. Would it be
possible for you to try # for the third section of a format (zero values) and
see if field on report is showing blanks for zero values when 0, 1 and 2
decimal places set for this field. Thanks in advance, your help much
appreciated.

 

[John Spencer]

If you use # for the third argument in the format, it shows
Blank with 0 decimal places
..0 with 1
..00 with 2
..000 with 3

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..