Select a maximum value from a calculated control 1

[theSizz]

I am trying to create a control in the Report Footer that will return the maximun value from a calculated control that resides in a group footer.
Specifically here's what I'm doing.
I have a field in the database named sales. Daily sales are entered into this field. The report is linked to a query that retrieves the daily sales.
The report has a group footer named Month that groups the sales by each month.
The control in the month footer named txtTotSales has this code in it's control source property =Sum([sales])
Every thing produces the expected results.
My problem lies when I try to insert a text box control in the Report Footer to retrieve the maximum figure from the list of sales produced for each month that is in the month footer.
When I place this code into the text control
=Max([txtTotSales])
I get a message asking for the parameter value of txtTotSales.
If I place this code:
=Max((Sales])
I get the maximum value for the sales for a given day, not the maximum value for the sales for the combined month.
I know it has something to do with the calculated value for the month, but I can't figure out how to achieve the desired results. Any help would be greatly appreciated. I'm stuck and up against a deadline to complete this project.
Thanks very much.

 

[dhookom]

Create a totals query that groups by month and totals sales. Then create a query that returns a single record that is the maximum of the month totals. Add this final query to your report's record source and expose the field to your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[theSizz]

Thanks Duane
That's the solution. Everything is working fine. I appreciate your help and quick response.