I need help getting a text box to show the max value on a calc column in a report. I created a calc column named "ProfitB" in the details section that is =[gp_rebate]*[totalqty]
[gp_rebate] is a field from the query and [totalqty] is a text box in the report footer with the calc =Sum([SUM_of_BILLED_QTY])

I need a text box to show the Max value in the ProfitB column. I have tried =Max([ProfitB]) but I get a parameter prompt. I have also tried =Max([gp_rebate]*(sum([sum_of_billed_qty]))) and I get "Cannot have aggregate function in expression..." Also, it doesnt seem to matter where i try to put this in the report, still doesnt work.

Can anyone help steer me in the right direction?

As you have discovered, what you are doing won't work.

If I were doing this, I would develop a query that calculated the gp_rebate * totalqty values for the report's recordset. Then use DLookup into that query to get the needed value. For instance, if the report is already based on a query, then you could just use DLookup into the same query to get the needed value, the criteria values will already be set.

You could also do it in code, I suppose, but I like the idea of not working that hard when I don't have to.



Edited by: fkegley on Tue Dec 1 15:41:03 EST 2009.

So are you saying I need a 2nd query to first sum the [sumof billed qty] then another calc in a query (which one?) to perform the other acalc and do a dlookup as an expression in the report? I havent done this before and I'm not familiar with how to use dlookup.

OOO! Never mind that. I got it! YAY Thanks so much for getting me in the right direction. You're awesome

You're welcome, Amy. I am glad I could help.