Format a Union Query Percentage in Microsoft Access
Question details
The user needs to display a decimal value from an Access union query as a percentage in a report.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A report uses a percentage field returned by a union query.
- Observed behavior
- The value may appear in scientific notation, and the report text box uses Val([Percent]) as its control source.
Open the union query in SQL View and identify the percentage column in each SELECT statement. Check whether the stored values are fractions, such as 0.125 for 12.5%.
Format the numeric union query field in the report
Use this approach to keep the query result numeric and control how the percentage appears in the report.
Open the query in SQL View. Make sure corresponding columns appear in the same order in every SELECT statement and that the percentage column returns compatible numeric types in each branch.
In the first SELECT statement, give the percentage column an alias such as Rate, and save the query. Avoid using Percent as a field or object name.
Open the report in Design View, select the percentage text box, and press F4 to open its Property Sheet.
On the Data tab of the Property Sheet, set Control Source to Rate or select the numeric field from the list. Remove the Val([Percent]) expression.
On the Format tab of the Property Sheet, set Format to Percent and choose the required Decimal Places. Save the report and open it in Report View to check the result.

Work with percentage data in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface for working with Office files. Use WPS Spreadsheets to format exported numeric data as percentages; it does not fix Access union queries or reports.

Frequently Asked Questions
Why does my percentage appear in scientific notation?
Access may display a numeric value in scientific notation depending on its format and size. In the report text box's Property Sheet, set Format to Percent and adjust Decimal Places.
Where do I change a report text box's Control Source?
Open the report in Design View, select the text box, press F4, and find Control Source on the Data tab of the Property Sheet.
Should I use Val([Percent]) in the report?
If the query already returns a numeric field, bind the text box directly to that field instead. Val converts an expression to a number but does not apply percentage formatting.
Why does 12.5 display as 1,250%?
Percentage formatting treats 1 as 100%, so 12.5 displays as 1,250%. Store or calculate the fractional value 0.125 if the intended result is 12.5%.




