Formula Errors

Fix Whole Numbers in a Microsoft Access Calculated Query Field

Huda QurayshiHuda Qurayshi Nov 22, 2026 868 views

Question details

The user needs a calculated Access query field to retain and display decimal values.

Fix Whole Numbers in a Microsoft Access Calculated Query Field
Product
Microsoft Access
Device & OS
not provided
Scenario
A query calculates WattsPerDay from Hours and Watts, then uses the result in energy and cost calculations.
Observed behavior
WattsPerDay displays whole numbers even though the field is set to show decimals. A calculation using numeric literals displays decimals.
Before you start

Open the query in Design View and identify where FormatNumber is used in the WattsPerDay expression. Check the stored Hours and Watts values as well as their field types; display formatting alone does not add precision to stored values.

Solution 1Recommended

Keep the calculation numeric and format the final result

Use this approach when FormatNumber is applied before the value is used in later calculations.

FormatNumber returns formatted text. Keep intermediate results numeric so later calculations do not depend on converting that text back to a number.

1
Open the query design

In the Navigation Pane, right-click the query and select Design View.

2
Replace the intermediate expression

In the query grid, replace the WattsPerDay Field expression with WattsPerDay: CDbl([Hours])*CDbl([Watts]). Remove FormatNumber from any value used in subsequent calculations.

3
Keep later calculations numeric

Update the KwhPerDay and CostPerDay expressions in the query grid to use numeric calculations rather than a FormatNumber result. If needed, repeat the underlying expression instead of relying on a formatted query alias.

4
Set the display format

Select the calculated column in Design View, open its Property Sheet, and set Format to Fixed and Decimal Places to the desired number. Apply display formatting to the final output rather than to intermediate calculations.

5
Test the result

Click Run and compare WattsPerDay with the source Hours and Watts values. For example, 4.1167 multiplied by 1200 should produce 4940.04.

Keep the calculation numeric and format the final result
Check for null values: CDbl cannot convert Null. If either source field can be Null, decide how missing values should be handled before applying the conversion.
Free Microsoft Office alternative

Calculate and display decimal values in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets. Open compatible Excel files and calculate or format decimal results in WPS Spreadsheets. It does not repair Microsoft Access queries.

Calculate energy and cost values with spreadsheet formulasSet the number of displayed decimal places for resultsOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface when moving from Microsoft Office
QA img-9

Frequently Asked Questions

Why should I avoid FormatNumber in an intermediate Access calculation?

FormatNumber returns text, so using its output in another calculation can introduce type conversion issues. Keep the expression numeric and set the final column's Format and Decimal Places properties in the query Property Sheet.

Why does a calculation with literals show decimals but my fields do not?

The stored field values, field types, or query expressions may differ from the literals. Add Hours and Watts to the query grid and inspect their source values and types before comparing the calculations.

Does setting Decimal Places to six change the calculated value?

No. The Decimal Places property controls display precision; it does not restore fractional digits that were already rounded or not stored.

What happens if Hours or Watts contains Null?

CDbl cannot convert Null. Check the source fields for missing values and decide whether to exclude those records or handle missing values explicitly in the query.