Revisiting: Calculated control value as a value in a filter

  • Thread starter Thread starter Joelk
  • Start date Start date
J

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
 
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
 
Back
Top