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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Rather than 'not print any records' I meant 'not show any records that have a
value of 0 UnitsOnHand in the Report'.
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)

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

andrewsos said:
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.
Click to expand...
Click to expand...
 
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

david epsom dot com dot au said:
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)

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

andrewsos said:
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.
Click to expand...
Click to expand...
Click to expand...
 
cannot use an aggregate function in a Where
condition (filter).
Click to expand...

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)

andrewsos said:
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

david epsom dot com dot au said:
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)

andrewsos said:
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.
Click to expand...
Click to expand...
Click to expand...
 
andrewsos said:
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?
Click to expand...


Try using the HAVING clause.
 
Back
Top