'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use AVERAGE with a Calculated Control in an Access Report Footer
Question details
The user needs to calculate the average of a calculated detail control (like a date difference) in an Access report footer but receives unexpected parameter value prompts.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Calculating an average value in an Access report footer based on computed data from the report details.
- Observed behavior
- The AVERAGE expression fails and prompts the user to manually enter parameter values instead of calculating the result automatically.
Ensure that your report's underlying record source (table or query) contains the raw fields needed for the calculation, as aggregate functions cannot directly reference unbound report control names.
Repeat the Calculation Inside the Aggregate Function Using Source Fields
Fix the parameter prompt by replacing report control references (like text box names) with the actual field names from your underlying table or query directly inside the AVERAGE function.
In Microsoft Access, aggregate expressions like Avg() or Sum() cannot resolve values from report controls (such as text boxes named with 'txt' prefixes). Instead, they must evaluate the fields from the report's record source.
To bypass the parameter prompt, you need to embed the mathematical calculation directly into the aggregate function using the original database field names.
Check the property sheet of your report to find the exact names of the fields in your underlying table or query (e.g., [DateSubmitted] and [DatePaid]), rather than the names of the text boxes displaying them (e.g., txtDateSubmit).
Right-click your report in the Access navigation pane and select 'Design View'.
Click on the unbound text box in the Report Footer where you want the average result to be displayed.
Open the Property Sheet, navigate to the 'Data' tab, and set the Control Source property to repeat the calculation. For example: =Avg(DateDiff("d", [DateSubmitted], [DatePaid])).
Save your changes and switch to 'Report View' or 'Print Preview' to verify that the average calculates without prompting for parameters.
Manage Data and Reports with WPS Office
While Microsoft Access is a specialized database tool, many data tracking, averaging, and reporting tasks can be efficiently handled using WPS Spreadsheet. WPS Office offers a free, lightweight, and highly compatible alternative to Microsoft Office, featuring advanced formulas and PivotTables for instant data summaries.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Import Your Data: Export your Access data to an Excel format and open it seamlessly in WPS Spreadsheet.
- 3. Calculate Averages Instantly: Use the =AVERAGE() function or insert a PivotTable to instantly calculate and report on your data without complex database expressions.
Frequently Asked Questions
Why does Access prompt for a parameter value when I use Avg() on a calculated control?
Access prompts for a parameter because aggregate functions like Avg() or Sum() cannot recognize report control names (such as an unbound text box named 'txtDateSubmit'). They must evaluate the actual fields stored in the report's underlying table or query.
Can I calculate an average in a page footer instead of a report footer?
No, aggregate functions like Avg() or Sum() cannot be directly placed in a page footer in Microsoft Access. They are specifically designed to calculate totals over a group of records, so they must be placed in a Report Footer or a Group Footer.
How do I average a standard field in an Access report?
To average a single, standard field without calculations, simply add a text box to your report footer and set its Control Source to =Avg([YourFieldName]), ensuring you use the exact field name from your database table.
