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

Write a criteria to your query so the query won´t give you zero values.

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

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

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.

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