Not showing records in a report that have a calculated 0 value

[Guest]

Hi,
I have been modifying the inventory control template located in Access
templates to suit our inventory control requirements. What I can't work out
is how to not print any records that have a value of 0 'UnitsOnHand'. The
UnitsOnHand is calculated from the sum of UnitsReceived minus the sum of
UnitsSold for each Item. I can display all records in the Report, but I would
like to only view those records where I still have units on hand, ie. not 0.
It appears I cannot create a filter based on the following calculation:
(Sum(nz[UnitsReceived])-Sum(nz[UnitsSold]))<>0
Any thoughts or suggestions would be greatly appreciated.
Andrew S.

 

[Guest]

Rather than 'not print any records' I meant 'not show any records that have a
value of 0 UnitsOnHand in the Report'.
Andrew

 

[david epsom dot com dot au]

You can modify the query to exclude those records.

Or you can code the Format event using the PrintSection,
MoveLayout, NextRecord properties.

Report filters are implemented by changing the query,
and in some cases Access is not able to work out how
to write the new query, but it is only a computer
program...

(david)

Rather than 'not print any records' I meant 'not show any records that
have a
value of 0 UnitsOnHand in the Report'.
Andrew

Hi,
I have been modifying the inventory control template located in Access
templates to suit our inventory control requirements. What I can't work
out
is how to not print any records that have a value of 0 'UnitsOnHand'. The
UnitsOnHand is calculated from the sum of UnitsReceived minus the sum of
UnitsSold for each Item. I can display all records in the Report, but I
would
like to only view those records where I still have units on hand, ie. not
0.
It appears I cannot create a filter based on the following calculation:
(Sum(nz[UnitsReceived])-Sum(nz[UnitsSold]))<>0
Any thoughts or suggestions would be greatly appreciated.
Andrew S.

 

[Guest]

Yes, modifying the query seems the logical step. I have been unsuccessful,
however in working out how to do this. Apparently you cannot use an aggregate
function in a Where condition (filter). What other ways are there to filter
based on the results of a calculated value?
Thanks
Andrew

You can modify the query to exclude those records.

Or you can code the Format event using the PrintSection,
MoveLayout, NextRecord properties.

Report filters are implemented by changing the query,
and in some cases Access is not able to work out how
to write the new query, but it is only a computer
program...

(david)

Rather than 'not print any records' I meant 'not show any records that
have a
value of 0 UnitsOnHand in the Report'.
Andrew

Hi,
I have been modifying the inventory control template located in Access
templates to suit our inventory control requirements. What I can't work
out
is how to not print any records that have a value of 0 'UnitsOnHand'. The
UnitsOnHand is calculated from the sum of UnitsReceived minus the sum of
UnitsSold for each Item. I can display all records in the Report, but I
would
like to only view those records where I still have units on hand, ie. not
0.
It appears I cannot create a filter based on the following calculation:
(Sum(nz[UnitsReceived])-Sum(nz[UnitsSold]))<>0
Any thoughts or suggestions would be greatly appreciated.
Andrew S.

 

[david epsom dot com dot au]

cannot use an aggregate function in a Where

condition (filter).

You can use aggregate functions in where conditions:
the tested function must one of the fields returned
by the query: you must design the query yourself,
(use VB code to re-write the SQL if required), because
you won't be able to use a report filter.

You will find it easier to code the query if you
created a simple select query based on the aggregate
query.

But even that won't help you with a report filter:
you must design and save the query yourself.

(david)

Yes, modifying the query seems the logical step. I have been unsuccessful,
however in working out how to do this. Apparently you cannot use an
aggregate
function in a Where condition (filter). What other ways are there to
filter
based on the results of a calculated value?
Thanks
Andrew

You can modify the query to exclude those records.

Or you can code the Format event using the PrintSection,
MoveLayout, NextRecord properties.

Report filters are implemented by changing the query,
and in some cases Access is not able to work out how
to write the new query, but it is only a computer
program...

(david)

Rather than 'not print any records' I meant 'not show any records that
have a
value of 0 UnitsOnHand in the Report'.
Andrew

:

Hi,
I have been modifying the inventory control template located in Access
templates to suit our inventory control requirements. What I can't
work
out
is how to not print any records that have a value of 0 'UnitsOnHand'.
The
UnitsOnHand is calculated from the sum of UnitsReceived minus the sum
of
UnitsSold for each Item. I can display all records in the Report, but
I
would
like to only view those records where I still have units on hand, ie.
not
0.
It appears I cannot create a filter based on the following
calculation:
(Sum(nz[UnitsReceived])-Sum(nz[UnitsSold]))<>0
Any thoughts or suggestions would be greatly appreciated.
Andrew S.

 

[Marshall Barton]

Yes, modifying the query seems the logical step. I have been unsuccessful,
however in working out how to do this. Apparently you cannot use an aggregate
function in a Where condition (filter). What other ways are there to filter
based on the results of a calculated value?

Try using the HAVING clause.