Set an Access Numeric Field to Display Two Decimal Places
Question details
The user wants an Access numeric field to display two decimal places and use cable lengths reliably in a tag calculation.

- Product
- Microsoft Access 2013
- Device & OS
- not provided
- Scenario
- Numeric values are imported from a text file and copied into a temporary table with a SELECT INTO query.
- Observed behavior
- Values such as 10 do not show trailing zeros, and a routine that searches for a decimal point fails on whole numbers. Attempts to use Double or Decimal may also produce errors.
Open the table in Design View and check the LENGTH field's data type. If the imported values are text, check for nonnumeric characters and decimal separators that do not match your regional settings before converting the field.
Format the Access field or control to show two decimal places
Use this method to display values such as 10 as 10.00 without changing their numeric value.
For floating-point measurements such as cable length, a Number field with the Double field size is generally appropriate. Numeric storage and display are separate: 10 and 10.00 represent the same value.
A SELECT INTO query creates a new table, so formatting applied to that table may need to be reapplied if the query deletes and recreates it.
In the Navigation Pane, right-click the table and select Design View. Select the LENGTH field.
Set Data Type to Number and, under Field Properties, set Field Size to Double if appropriate. If conversion fails, inspect the imported values for text or invalid numeric characters.
Under Field Properties, set Format to Fixed and Decimal Places to 2. Save the table and open it in Datasheet View to check the display.
If the value appears in a form or report, open it in Design View, select the LENGTH control, and use its Property Sheet to set Format to Fixed and Decimal Places to 2.

Calculate the number of cable tags without searching for a decimal point
Use arithmetic instead of the displayed value when calculating one tag for each started 10 units of cable.
Format cable-length data in WPS Spreadsheets
WPS Office is a free, lightweight alternative for working with spreadsheet data in a familiar interface. Open compatible Excel files and format numeric columns with two decimal places for an easy transition from Microsoft Office. WPS does not modify Access database field properties.

Frequently Asked Questions
Why does Access show 10 instead of 10.00?
Both represent the same numeric value. In table Design View, set the field's Format property to Fixed and Decimal Places to 2 to show trailing zeros.
Does setting Decimal Places to 2 round the stored value?
No. The display setting changes how the value appears, not its underlying precision. Calculations can still use the stored value.
Why might changing an imported field to Number fail?
The field may contain nonnumeric text or decimal separators that do not match the system's regional settings. Inspect and correct the imported values before changing the data type in Design View.
How can I calculate tags when the length is a whole number?
Use the numeric expression -Int(-([LENGTH]/10)) rather than searching the displayed value for a decimal point. For example, 10 produces 1 and 49.25 produces 5.
Will the format remain if my SELECT INTO query recreates the table?
SELECT INTO creates a new table. If your process deletes and recreates it, check and reapply the field's display properties, or format the control that displays the value in a form or report.




