Others

How to Format Access Numbers with Thousands Separators and Optional Decimals

Maira Mehtab Oct 1, 2026 868 views

Question details

The user needs to format numeric values in a database form or report to include thousands separators and dynamically display decimals only when the value is not a whole integer.

Format Access Numbers with Thousands Separators and Optional Decimals

Product: Microsoft Access
Device & OS: not provided
Scenario: Customizing the display of numeric data on user forms or printed reports to prevent whole numbers from showing unnecessary decimal zeroes.
Observed behavior: Standard formatting options either force a fixed number of decimal places for all values or remove decimals entirely, requiring a conditional workaround.

Before you start

Ensure you have Design View access to the form, report, or query where you want to apply the formatting, and note the exact name of the field containing your numeric data.

Solution 1Recommended

Use an IIf Expression in a Control Source or Query

Apply conditional formatting logic using the IIf and Int functions to dynamically format the number based on whether it has a fractional value.

Because Microsoft Access does not have a single standard format that conditionally hides decimals for integers while keeping them for fractions, you must use an expression. The Int() function helps determine if a number is whole by comparing the integer version of the number to the number itself.

Open Design View

Open your Access database and switch your target form, report, or query into Design View.

Access the Property Sheet

Select the text-box control where the number is displayed and press F4 to open the Property Sheet.

Locate the Control Source

Navigate to the Data tab within the Property Sheet and locate the Control Source property.

Enter the Conditional Logic

Enter the following expression, replacing [FieldName] with your actual field name: =Format([FieldName], IIf(Int([FieldName])=[FieldName], "#,##0", "#,##0.00"))

Save and Verify

Save your changes and switch to Form View or Report View to confirm that whole numbers display as 1,234 and fractional numbers display as 1,234.56.

Use an IIf Expression in a Control Source or Query

Formatting Tip: You can easily adapt this logic for currency by adding a symbol inside the format strings, for example: "$#,##0" and "$#,##0.00".

Solution 2

Create a Custom VBA Formatting Function

If you need to apply this dynamic format across multiple forms and reports, a custom VBA function provides a reusable and cleaner approach.

Free Microsoft Office alternative

Manage Data Easily with WPS Office

While complex relational databases require specialized software, many data management tasks are much easier to handle in a powerful spreadsheet. WPS Spreadsheet offers intuitive, built-in custom number formatting without the need for complex expressions or VBA coding.

1. Download the Installer: Visit the official WPS Office website and download the free installation package.
2. Install the Software: Run the setup file and follow the on-screen instructions to install WPS Office on your device.
3. Open WPS Spreadsheet: Launch WPS Spreadsheet, import your data, and use the custom cell formatting tools to easily display numbers exactly how you want them.

Completely free and lightweight Office suite for all your document needs.Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) file formats.Built-in advanced cell formatting easily handles dynamic decimal places and thousands separators.Familiar tabbed interface requiring zero learning curve for new users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard Access number format show decimals for whole numbers?

The built-in 'Standard' format in Access is designed to align numbers uniformly in columns, which typically enforces a fixed number of decimal places (usually two). This causes whole numbers to display with a '.00' trailing extension.

Can I format numbers dynamically in the Access table design view?

Table-level format properties are generally static and do not support complex conditional logic like the IIf statement. It is best practice to store the raw data in the table and apply dynamic formatting at the presentation layer (queries, forms, or reports).

How does the Int() function help format numbers?

The Int() function returns only the integer (whole) portion of a number. By comparing the output of Int([FieldName]) to the original [FieldName], the database can logically determine if the number contains a fraction and apply the appropriate format string.

Will this formatting affect how the data is saved in the database?

No. Using the Format() function only changes how the data is visually represented on the screen or printed page. The underlying numeric value stored in your database tables remains completely unchanged and accurate.

Office Quick Solutions