Hiding 0 Values In Reports

[trfconsulting]

I have a report that I am using for invoicing, and there are 8 generated
fields for description, invoice periods, units & costs. I would like to have
this report print without the zero value for those lines that are not used,
but can't seem to get it to work. If I suppress zero values by taking out the
0 in the forms page, then the formulaes won't calculate properly for
calculating tax & totals.
Does anyone know if there is a way to suppress (hide) zero values, like you
can in Excell, in Access Reports?
Thanks

 

[Ken Hudson]

Hi Trf,

Not a perfect solution but.....
In the report design view, put a new text box in your detail section that
uses the units field as the Control Source. Go to the format properties of
this text box and change the Visible property to No.

In the Control Source for the "real" units field in your report enter:

=IIf([text11]=0,"",[text11])

where "text11" is the name of your hidden text box.

HTH

Cheers.

 

[Rick B]

I don't understand what your report looks like. You say to "print without
the zero value for those lines not used".
If they are "not used" dosn't that mean they do not print?

I can't picture your report.

Share a couple of lines of real data with us.

Rick B

 

[trfconsulting]

Rick,
The invoice report page has 8 rows of item cirteria, under 5 fields. They
are Description, Invoice Period (From/To), Units & Amount.
The problem is, if I only have 1 item for a customer, the remaining 7 rows
will show 0 in units and $0.00 in amount. I can't remove the zero in the form
entry, or else it won't calculate for totals, since the field is blank. I've
tried using iif statements to supress if the value is zero, and that works,
"taking out" (hiding" the entries that are zero and leaving the others. Onlt
problem with using that method is that once I use an iif statement, it treats
it as text, and then won't format as standard for units & currency for amount.
I know that in Excel you can go to spreadsheet options and suppress zeros,
so that they do not show on the spreadsheet...but I've yet to find where this
can be done in Access.
Any information that you can give would be greatly appreciated.
Thanks

 

[trfconsulting]

Ken,
I had actually tried this, to a degree, earlier, but not with referencing
another txt field. The problem that I had run into was that the iif statement
created the vale as text, which would then not let me format for standard &
currency.
It's not the most "accurate" fix, nor problem a recommended one by
programmers, but it gets the job done! Thanks for your information and
response!
Tony

Hi Trf,

Not a perfect solution but.....
In the report design view, put a new text box in your detail section that
uses the units field as the Control Source. Go to the format properties of
this text box and change the Visible property to No.

In the Control Source for the "real" units field in your report enter:

=IIf([text11]=0,"",[text11])

where "text11" is the name of your hidden text box.

HTH

Cheers.

I have a report that I am using for invoicing, and there are 8 generated
fields for description, invoice periods, units & costs. I would like to have
this report print without the zero value for those lines that are not used,
but can't seem to get it to work. If I suppress zero values by taking out the
0 in the forms page, then the formulaes won't calculate properly for
calculating tax & totals.
Does anyone know if there is a way to suppress (hide) zero values, like you
can in Excell, in Access Reports?
Thanks

 

[helios]

I found your post while I was musing about how it would be nice not to see
all of those zeroes in my report.

I found a neat little solution. Use conditional formatting to change the
text color to white where the value is equal to 0. Your data is still there
to use in calculations, but you won't see it on a white background or when
printing. Worked liek a charm for me and took about 10 seconds to do!

-Sunshine

 

[trfconsulting]

Thanks for your input solution on this issue. Actually, the solution offered
by Ken (see above) was the most useful of them all, and works wonderfully. As
for your specific solution, if this were a spreadsheet, I would do it in that
method, because conditional formatting will give me formatting options to
additional filtering. However, I was not aware that you could use conditional
formatting within a report in access?
Thanks again for your input.

 

[Bob Barnes]

Great Info but could helios write an example of the conditional formatting?
Thanks!

 

[Bob Barnes]

Great Info, can helios write a brief example of the conditional formatting
and where to apply it?

Thanks!

 

[Duane Hookom]

Conditional Formatting can be found under the Format menu while in design
view. It is fairly obvious what you need to do.

Another method that I would find much easier is setting the Format property
to hide 0 values. Search Help on Format Numbers.

 

[helios]

Select the textbox(es) you'd like to suppress the zeroes from appearing in
and click on Format (Alt + O), select Conditional Formatting from the
dropdown list (Alt + D).
A dialog box (similar to the conditional formatting available in Excel)
lists the current formatting at top, and options for the criteria to be met
for the conditional formatting at bottom.
To suppress zeroes from appearing onscreen/printing by this "whiteout" method:

set condition to "Field Value" is "Equal to" "0" and set the formatting
text color to white (via the textcolor icon "A" at right) when this condition
is met. You can also use "expression" instead of the field value to set more
complex criteria based on whether an expression is true or false.

You can set up to three conditions with unique formatting (conditions listed
first take precedence over subsequent conditions).

Select multiple textboxes to apply the same conditional formatting to all of
them.

I've used conditional formatting for other handy low-tech solutions like
warning a user of possible incorrect entries by changing the background color
of the textbox to orange, etc. I hope you find it a handy tool as well.

-Sunshine

 

[Zac Woodall [MSFT]]

An alternative solution is to bind the field's RecordSource this way
=IIF([YourField]>0, [YourField], Null)

 

[Bob Barnes]

Perfect Duane... Thanks for your assistance!

Bob

 

[Bob Barnes]

Thanks helios!