Formatting Issues

Format a Union Query Percentage in Microsoft Access

Kushani NimanthikaKushani Nimanthika Nov 22, 2026 868 views

Question details

The user needs to display a decimal value from an Access union query as a percentage in a report.

Format a Union Query Percentage in Microsoft Access
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.
Before you start

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%.

Solution 1Recommended

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.

1
Check the union query

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.

2
Use a clear field name

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.

3
Open the report properties

Open the report in Design View, select the percentage text box, and press F4 to open its Property Sheet.

4
Bind the numeric field

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.

5
Apply percentage formatting

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.

Format the numeric union query field in the report
Check the underlying scale: Percent formatting displays 0.125 as 12.5%. If the stored value is 12.5 instead, it represents 1,250% when formatted as a percentage.
Free Microsoft Office alternative

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.

Format numeric spreadsheet cells as percentages with adjustable decimal placesOpen and edit Microsoft Excel formats, including XLSX and XLSUse familiar spreadsheet tools to ease the move from Microsoft OfficeWork with exported report data in a lightweight office suite
microsoft office alternative - wps office

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%.