Revisiting: Calculated control value as a value in a filter

[Joelk]

My original post:

The report I'm creating is grouped by customers, then orders, and the
detail is individual order items. There is a footer for each order.

That footer contains a control that calculates the total gross margin
of the items in the detail section of the report.

I want to filter the report so that only the groups with a total gross
margin of less than a specified amount appear.

What I can't get to work is the reference to the value of the
calculated control for gross margin.

Suggestions?

-----------------------------
I still can't get this to work. Thanks Duane, but you suggestion
didn't quite get what I wanted.)

Here's kind of what I want the report to look like. If the gross
margin for an order is below a specified value, then don't list that
order. All the values except for the totals are from a query that
draws from a couple of tables with order item info, customer info
etc.:

GROUPHDR SALESPERSON
GROUPHDR CLIENT
GROUPHDR SALESORDER
DETAIL: ITEM;PRICE;COST;MARGIN (calculated)
GROUP FOOTER SALESORDER: ORDER TOTAL; GROSS MARGIN FOR ORDER
(CALCULATED)

Once again, any suggestions as to how accomplish this are appreciated!

Thanks -

Joel

 

[Nikos Yannacopoulos]

Joel,

You can't filter a report on a calculated field, you can only filter on a
field that comes in its recordsource. My suggestion:

Make a totals query that calculates the GM per order at header level;
Join this with the line items table in a new query;
Now use the latter as your report's recordsource, so the GM per order is
already calculated and repeated in each row, and you can filter on it!

HTH,
Nikos