Fix Whole Numbers in a Microsoft Access Calculated Query Field
Question details
The user needs a calculated Access query field to retain and display decimal values.

- 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.
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.
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.
In the Navigation Pane, right-click the query and select Design View.
In the query grid, replace the WattsPerDay Field expression with WattsPerDay: CDbl([Hours])*CDbl([Watts]). Remove FormatNumber from any value used in subsequent calculations.
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.
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.
Click Run and compare WattsPerDay with the source Hours and Watts values. For example, 4.1167 multiplied by 1200 should produce 4940.04.

Check the source values and query display settings
Use this check if the expression remains numeric but the result still appears rounded.
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.

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.




