Exclude records by calculated field

Hi I'm using the demo ordering system and have copied the invoice report and
have got it to run for all records. But now i want to exclude records that do
not have an outstanding amount. eg Total Due 0 As this is a calculated
field within the report i dont seem to be able to use it in the query and the
filter doesn't seem to work but i am a newbie. Please advise

You should be able to create a totals query grouped by Invoice that
calculates the total. Add this query to your report's record source so you
can use the calculated total for filtering.
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi I'm using the demo ordering system and have copied the invoice report and
have got it to run for all records. But now i want to exclude records that do
not have an outstanding amount. eg Total Due 0 As this is a calculated
field within the report i dont seem to be able to use it in the query and the
filter doesn't seem to work but i am a newbie. Please advise

Hi Duane, The report already has the fields calculated 'Total Due' but i
can't seem to set up a filter, i've tried following several examples but
getting no where.



"Duane Hookom" wrote:

You should be able to create a totals query grouped by Invoice that
calculates the total. Add this query to your report's record source so you
can use the calculated total for filtering.
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi I'm using the demo ordering system and have copied the invoice report and
have got it to run for all records. But now i want to exclude records that do
not have an outstanding amount. eg Total Due 0 As this is a calculated
field within the report i dont seem to be able to use it in the query and the
filter doesn't seem to work but i am a newbie. Please advise

When you look at the "field list" in the report design, do you see a field
named "Total Due"?

If "Total Due" In (Field List) Then
Use it
report back results or issues
Else
GoTo my previous reply
End If
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi Duane, The report already has the fields calculated 'Total Due' but i
can't seem to set up a filter, i've tried following several examples but
getting no where.



"Duane Hookom" wrote:

You should be able to create a totals query grouped by Invoice that
calculates the total. Add this query to your report's record source so you
can use the calculated total for filtering.
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi I'm using the demo ordering system and have copied the invoice report and
have got it to run for all records. But now i want to exclude records that do
not have an outstanding amount. eg Total Due 0 As this is a calculated
field within the report i dont seem to be able to use it in the query and the
filter doesn't seem to work but i am a newbie. Please advise

No it isn't in the fields list it is a calculated field that only has a value
in it once all the records for that customer have been listed and totaled eg.

Cust 1
purchase 1 amount 1
purchase 1 amount 1
etc.
Total Due - (calculated from items above)

if this Total Due is zero i don't want to print the page



"Duane Hookom" wrote:

When you look at the "field list" in the report design, do you see a field
named "Total Due"?

If "Total Due" In (Field List) Then
Use it
report back results or issues
Else
GoTo my previous reply
End If
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi Duane, The report already has the fields calculated 'Total Due' but i
can't seem to set up a filter, i've tried following several examples but
getting no where.



"Duane Hookom" wrote:

You should be able to create a totals query grouped by Invoice that
calculates the total. Add this query to your report's record source so you
can use the calculated total for filtering.
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi I'm using the demo ordering system and have copied the invoice report and
have got it to run for all records. But now i want to exclude records that do
not have an outstanding amount. eg Total Due 0 As this is a calculated
field within the report i dont seem to be able to use it in the query and the
filter doesn't seem to work but i am a newbie. Please advise

Did you understand or even try my suggestion?

--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

No it isn't in the fields list it is a calculated field that only has a value
in it once all the records for that customer have been listed and totaled eg.

Cust 1
purchase 1 amount 1
purchase 1 amount 1
etc.
Total Due - (calculated from items above)

if this Total Due is zero i don't want to print the page



"Duane Hookom" wrote:

When you look at the "field list" in the report design, do you see a field
named "Total Due"?

If "Total Due" In (Field List) Then
Use it
report back results or issues
Else
GoTo my previous reply
End If
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi Duane, The report already has the fields calculated 'Total Due' but i
can't seem to set up a filter, i've tried following several examples but
getting no where.



"Duane Hookom" wrote:

You should be able to create a totals query grouped by Invoice that
calculates the total. Add this query to your report's record source so you
can use the calculated total for filtering.
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi I'm using the demo ordering system and have copied the invoice report and
have got it to run for all records. But now i want to exclude records that do
not have an outstanding amount. eg Total Due 0 As this is a calculated
field within the report i dont seem to be able to use it in the query and the
filter doesn't seem to work but i am a newbie. Please advise

Hi Duane sorry for being such a newbie and yes i did try your suggestion and
it is not in the field list and yes i probably don't understand.

The field does not exist or have any value in it untill the page is complete
and the calculations have been done. How can i test such a field?


"Duane Hookom" wrote:

Did you understand or even try my suggestion?

--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

No it isn't in the fields list it is a calculated field that only has a value
in it once all the records for that customer have been listed and totaled eg.

Cust 1
purchase 1 amount 1
purchase 1 amount 1
etc.
Total Due - (calculated from items above)

if this Total Due is zero i don't want to print the page



"Duane Hookom" wrote:

When you look at the "field list" in the report design, do you see a field
named "Total Due"?

If "Total Due" In (Field List) Then
Use it
report back results or issues
Else
GoTo my previous reply
End If
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi Duane, The report already has the fields calculated 'Total Due' but i
can't seem to set up a filter, i've tried following several examples but
getting no where.



"Duane Hookom" wrote:

You should be able to create a totals query grouped by Invoice that
calculates the total. Add this query to your report's record source so you
can use the calculated total for filtering.
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi I'm using the demo ordering system and have copied the invoice report and
have got it to run for all records. But now i want to exclude records that do
not have an outstanding amount. eg Total Due 0 As this is a calculated
field within the report i dont seem to be able to use it in the query and the
filter doesn't seem to work but i am a newbie. Please advise

My suggestion:
You should be able to create a totals query grouped by Invoice that
calculates the total. Add this query to your report's record source so you
can use the calculated total for filtering.

Can you create a totals query? It should be similar to your report's record
source only group by the field where your current calculated value resides.

--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi Duane sorry for being such a newbie and yes i did try your suggestion and
it is not in the field list and yes i probably don't understand.

The field does not exist or have any value in it untill the page is complete
and the calculations have been done. How can i test such a field?


"Duane Hookom" wrote:

Did you understand or even try my suggestion?

--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

No it isn't in the fields list it is a calculated field that only has a value
in it once all the records for that customer have been listed and totaled eg.

Cust 1
purchase 1 amount 1
purchase 1 amount 1
etc.
Total Due - (calculated from items above)

if this Total Due is zero i don't want to print the page



"Duane Hookom" wrote:

When you look at the "field list" in the report design, do you see a field
named "Total Due"?

If "Total Due" In (Field List) Then
Use it
report back results or issues
Else
GoTo my previous reply
End If
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi Duane, The report already has the fields calculated 'Total Due' but i
can't seem to set up a filter, i've tried following several examples but
getting no where.



"Duane Hookom" wrote:

You should be able to create a totals query grouped by Invoice that
calculates the total. Add this query to your report's record source so you
can use the calculated total for filtering.
--
Duane Hookom
Microsoft Access MVP


"Smiffy" wrote:

Hi I'm using the demo ordering system and have copied the invoice report and
have got it to run for all records. But now i want to exclude records that do
not have an outstanding amount. eg Total Due 0 As this is a calculated
field within the report i dont seem to be able to use it in the query and the
filter doesn't seem to work but i am a newbie. Please advise