Help with Access report that sums non zero values

[jeffj13]

Hello,



I am MS Access newbie and working with a set of reports that were created by someone else that are part of a crosstab query. I am trying to adjust one of the formulas in the report to sum the above rows in the report when that row meets a condition where it is greater than 0. Right now the formula is summing all of the above rows and reads as follows.

=Sum(IIf(IsNull([MILES_DEST_ORIG]),0,[BA])) "MILES_DEST_ORIG" is basically the field that if it is greater than 0 I would like to sum all of the categories for that particular row. "BA" represents a column of data from the report that is generated from the crosstab query. As I mentioned right now it is basically just summing everything regardless if MILES_DEST_ORIG is 0 or not.

Hopefully I have explained this fully. Any help is much appreciated!

[ranman256]

build a query, Q1
put in the fields you want and the sum and the criteria.
save the query
if you want non-zero items, make Q2 that uses Q1 and set the sum field <>0

Q2 now has non-zero sums.
you can even run a Dlookup on it if you need.

[jeffj13]

build a query, Q1
put in the fields you want and the sum and the criteria.
save the query
if you want non-zero items, make Q2 that uses Q1 and set the sum field <>0

Q2 now has non-zero sums.
you can even run a Dlookup on it if you need.

Thanks for your reply. Is there a way I could adjust that formula instead? I have about 18 reports that generate data from an existing query and a crosstab query. With time constraints I would rather adjust the formula on each rather than recreate these reports.

[aytee111]

A query is the easiest way to solve this. How many times does this total appear on each report? In the query use parameters to match a key field on a form somewhere so that the same query can be used in all reports. Then in the control source use DLookup to get the value.

[jeffj13]

A query is the easiest way to solve this. How many times does this total appear on each report? In the query use parameters to match a key field on a form somewhere so that the same query can be used in all reports. Then in the control source use DLookup to get the value.

It appears 18 times on the crosstab report and then once each on the other 18 reports. I assumed it was easy enough to change some of the language with this formula =Sum(IIf(IsNull([MILES_DEST_ORIG]),0,[BA])) to sum the values of BA where MILES_DEST_ORIG was greater than 0.