Excluding Zero value records from report (1 Viewer)

tristo

New member
Local time
Today, 16:27
Joined
Oct 28, 2009
Messages
3
Hi All,

Hope someone can help with this query. I have a report that i need to alter to exclude 0 values however its not just a case of changing the format property(i think) as i want the whole line excluded not just the 0 value surpressed.

the report works off a query and the control source for the text box in question reads =Sum([Balance])
this value returns with a 0 on occassion and i would like to exclude this line when this happens.

I have attached a picture that may explain the issue a bit better


Thanks for having a look
and any help is appreciated
Tristo
 

Attachments

  • prob.PNG
    prob.PNG
    21.3 KB · Views: 202

olxx

Registered User.
Local time
Today, 08:27
Joined
Oct 2, 2009
Messages
36
Write a criteria to your query so the query won´t give you zero values.
 

tristo

New member
Local time
Today, 16:27
Joined
Oct 28, 2009
Messages
3
Write a criteria to your query so the query won´t give you zero values.


Hi Olxx,

Thanks for the reply but the problem seems to be with the report summing rather than the query as there is already a criteria in the query excluding zeros.
the zeros only come about as there is a Sum in the report. i.e i may have 2 records grouped one with +1000 value and the other with -1000 value these sum to zero only at the report stage.

Thanks
Tristo
 

DCrake

Remembered
Local time
Today, 16:27
Joined
Jun 8, 2005
Messages
8,634
Create an additional query that sums the balance in the earlier query grouped by PK and add a conditon <> 0 to the summed column. Then add this query to the first query and join on PK. This should only present you with accounts either a positive or negative balance.

As a matter of interest if you are sending out statements based off this query you may want to consider filtering it to balances over a certain minimum value. As customers take umbridge at being sent a statement for 50p. Also it may cost you more than 50p to send the statement.

David
 

olxx

Registered User.
Local time
Today, 08:27
Joined
Oct 2, 2009
Messages
36
OK. I´m pretty sure you can´t do it on report or maybe only with some tricky VBA code. Can´t you do the summing with query? Can you post your db here, maybe if i take a look at it i got some idea.
 

tristo

New member
Local time
Today, 16:27
Joined
Oct 28, 2009
Messages
3
Hi David / Olxx

thanks for the replies , i will try the additional query route although i tried something similar without luck but i think i may have left something out. The report is only for internal use but every cent has to be accounted for , the zeros are more so an anoyance than a major issue but one thats been raised all the same. wont be able to post the db unfortunately. will let you know how i get on
Thanks

Tristo
 

Users who are viewing this thread

Top Bottom