Full Version: Max Calc in Report
UtterAccess Discussion Forums > Microsoft® Access > Access Reports
adavis24
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])
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?
fkegley
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.
adavis24
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.
adavis24
OOO! Never mind that. I got it! YAY Thanks so much for getting me in the right direction. You're awesome
fkegley
You're welcome, Amy. I am glad I could help.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.