Conditional Formatting doesn't pass to next group

[DBS1]

Good morning,


I have a report that tracks weekly inventory for vendors. The report is pulling from qryWeeklyInventory. The fields in the query are Vendor, Day, and AmtOnHand. I have the report grouped by Vendor, and have a conditional format to color code the Vendor field blue for amounts over 35. The rule I have created is "Expression is ([AmtOnHand])>=35" (quotation marks are not part of the equation, just to hightlight what the expression is). This works fine for the first Vendor that has 35 on hand, but doesn't pass it on to the next Vendor. I have moved the Vendor box out of the Vendor group header and into the detail section as a test, and it then passes the rule on to the other vendors. It looks as if the conditional formatting isn't passing on to the next group. How would I go about fixing this?

[DBS1]

Upon further investigation, I find that the problem isn't what I thought it was, but that the expression is only looking at the first record for each vendor, and if it doesn't meet the criteria, moves on to the next vendor. I need the expression to look at the AmtOnHand for the vendor, on either one of the 7 records (day 1 through day 7) and return a result based on a match of the criteria with any of those records.

[Minty]

If you can, depending on how our data is stored and queried, include a Max([AmtOnHand]) in the vendor header and use that as you CF trigger.

You may need to use a sub query to get it into your normal query results.

[Gicu]

You might want to create a new totals query grouping by Vendor and getting Max(AmtOnHand) for the week; then bring that query into the report's record source by joining on Vender(ID) and use that field for the conditional formatting.

EDIT: Minty beat me to it...
You can also try to use a DMax("AmtOnHand","qryWeeklyInventory","VendorID =" & [VendorID])
Cheers,

[DBS1]

That did it. Thanks Minty and Vlad. Much appreciated.