count expression results in Report and leave zeros blank in the to

[JayStar]

I have a query in which there is a calculated expression based on two other
fields. If true, the return is 1. I want to sum or count (either will do,
but only count seems to work) these totals in a report. But it looks messy
because there are a number of zeros all over the place where the return is
not true. I would like to blank out the zeros so that only the positive
numbers show in the report. The Subtotal and Grand Totals are unaffected and
only count the positive numbers (I've checked), so blanking out the zeros
should have no effect on the sub and grand totals. I've tried nz = "" and
I've tried IfNull, but either they don't work with calculated expressions, or
I'm doing them wrong.

Has anyone any solution?

 

[Jeff Boyce]

I may not understand your situation...

It sounds like you could use a criterion in the query for the calculated
expression/field, and only select the rows where that calculated value = 1.
Would that eliminate the 0's?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[JayStar]

I tried setting criteria but the database froze (it sulked I guess). There
are actually 15 different calculations base on 15 groups of two other fields.
I have managed to do it all and the reports. Its just that with 15
calculated columns plus the date and description fields for each row, it
looks something like a nightmare with all the zeros on the "detail" row on
the Report. The summary report which doesn't have a detail row, just the
totals, looks almost civilised.

Iguess what I'm hoping for is something like conditional formatting in Excel
so that anything other than 1 is blank..................

I do wish Access was more like Excel.............. never mind, I'm groping
towards the light......

If you (or anyone) can offer more advice, I would be grateful....

 

[Jeff Boyce]

Access is a relational database... expecting it to work like Excel is ...
(fill in your description here).<g>

Access offers conditional formatting (check Access HELP).

Access offers formatting variations (i.e., what to show for positive,
negative, zero, null values) (check Access HELP). The general approach in
the format property for a control is: format; format; format; format (isn't
that similar to Excel's?).

Good luck!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[JayStar]

Success ! many thanks. I just tried it out on part of the database and all
the horrid zeros disappeared. I'll work on the rest and check it against
what was showing before (just to satisfy myself that I've done it correctly),
then my smile will be a mile wide.

many thanks.