Dec 1 2009, 03:31 PM
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?
Dec 1 2009, 03:40 PM
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.
Dec 1 2009, 03:49 PM
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.
Dec 1 2009, 03:52 PM
OOO! Never mind that. I got it! YAY Thanks so much for getting me in the right direction. You're awesome
Dec 1 2009, 05:12 PM
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