Others

How to Format Percentages Without Decimal Places in Access Queries

Kushani Nimanthika Sep 30, 2026 868 views

Question details

The user needs to display percentage values as whole numbers in a Microsoft Access query.

How to Format Percentages Without Decimal Places in Access Queries

Product: Microsoft Access
Device & OS: not provided
Scenario: Formatting query results to show clean, whole-number percentages for reporting or display purposes.
Observed behavior: The FormatPercent function currently returns percentages with two decimal places by default, such as 12.13% and 5.00%.

Before you start

Ensure your Access database is open and you have permissions to edit the query in Design View.

Solution 1Recommended

Modify the FormatPercent Function Arguments

Adjust the FormatPercent expression directly in your query design to specify zero decimal places.

By default, the FormatPercent function in Microsoft Access includes two decimal places. You can easily override this behavior by explicitly defining the 'NumDigitsAfterDecimal' argument.

Open Query Design View

Open your Microsoft Access database, locate the query in the Navigation Pane, right-click it, and select **Design View**.

Locate the Expression

Find the column in the query grid that contains your `FormatPercent` expression.

Add the Decimal Argument

Click into the expression and add `, 0` as the second argument inside the function parentheses. For a standard calculation, it will look like this: `FormatPercent([Value1]/[Value2], 0)`.

Handle Nulls and Zeroes (Optional)

If your expression needs to handle nulls or avoid division by zero, format it with `IIf` and `Nz`. Example: `IIf(Nz([Total1])+Nz([Total2])=0, "", FormatPercent(Nz([Total1])/Nz([Total2]), 0))`.

Run the Query

Click the **Run** button (!) located in the Query Design ribbon to execute the query. Your results will now display as whole-number percentages (e.g., 12% instead of 12.13%).

Modify the FormatPercent Function Arguments

Syntax Reminder: The second argument in the FormatPercent function represents the number of digits after the decimal point. Setting it to 0 guarantees a whole number.

Free Microsoft Office alternative

Analyze and Format Data Easily with WPS Office

While Microsoft Access is a specialized database tool, many data analysis and formatting tasks can be efficiently handled in WPS Spreadsheet. WPS Office is a free, lightweight suite that offers robust data processing capabilities, intuitive cell formatting, and seamless compatibility with Microsoft Excel files.

Format numeric data and percentages quickly using built-in visual cell formatting tools without writing complex functions.Highly compatible with Microsoft Excel formats (.xlsx, .xls, .csv) for seamless data importing and exporting.Lightweight and runs smoothly on Windows, Mac, and Linux systems.Free to use with a familiar, easy-to-navigate Ribbon interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I format percentages without decimals directly in the Access table design?

Yes, you can set the field's Format property to 'Percent' and the Decimal Places property to '0' in the Table Design view. This affects how the raw numerical data is displayed by default without altering the underlying value.

Why does FormatPercent return a string instead of a numerical value?

The FormatPercent function is specifically designed to return a formatted string for display purposes. If you need to perform further numerical calculations on this field, it is better to perform the raw math in the query and apply percentage formatting at the Form or Report level.

How do I prevent 'Division by Zero' errors when calculating percentages?

Use the IIf function to check if the denominator equals zero before performing the division. For example, use an expression like `IIf([Denominator]=0, 0, [Numerator]/[Denominator])` to safely bypass zero-division errors.

Office Quick Solutions