The small, humble decimal point is oddly the source of an enormous amount of confusion in the Access world. The difficulties are twofold. First, there is often a difference between how many decimal places are stored in Access and how many are displayed and visible. Second, various properties control the storage and display of decimal places and vitally affect one another. Figuring out just how they interact can be perplexing.

This tutorial seeks to allay some of the confusion. You will work with the Field Size, Format, and Decimal Places properties for a field with a Number data type to see how decimal places are stored and displayed under various settings. It won’t provide all the answers for every situation, but it should give you a good head start if decimal places are important to your work.

Store & Display Integers

Choose File and New. In the New File pane, click Blank Database. In the File New Database dialog box, navigate to a convenient folder. TypeDecimal Place Tutorial in the File Name field and click Create. With the Tables button selected, double-click Create Table In Design View.

In the first row of the Field Name column, typeLong_Integer. Press the TAB key. Open the drop-down menu for the Data Type column and choose Number. Leave the Description column blank; as we go along, you can use this optional column for any notes you may have. Choose File and Save. TypeDecimal Places and click OK. Click No in the message that asks if you want a primary key.

In the Field Properties below, click in Field Size, open the drop-down list, and choose Long Integer (it may already be selected). Click Save. Click the View button at the far left of the toolbar to go to Datasheet view.

Highlight 0 in the Long Integer column and type 34.5000. Press the TAB key. Access both displays and stores 34. Highlight 0 and type34.5001. Press TAB again. Access both displays and stores 35. Because the Field Size is a Long Integer, Access merely rounds the decimal places to the nearest integer (the same would be true if the Field Size were Byte or Integer). The numbers to the right of the decimal place are neither displayed nor stored.

Store & Display Numbers With Decimal Places

Click Save and click the View button to go to Design view. Click in the second row under the Field Name column and typeSingle_Fixed. Press the TAB key, open the drop-down menu, and choose Number. In Field Properties, click in Field Size, open the drop-down menu, and choose Single. Press TAB, open the drop-down menu in the Format row, and choose Fixed. Note that the Decimal Places property is set to Auto. Click Save and then click the View button to go to Datasheet view.

Click in the first row of the Single_Fixed column. Type8.7789. Click in the same column in the row below. The number you just typed is stored as 8.7789 but displayed as 8.78. Type8.5001and press ENTER. The number is stored as 8.5001 and displayed as 8.50.

What does this example tell you? In contrast to the field sizes that store only integers, the Single setting is precise to seven decimal places (for the Double setting, it’s 15). If the Decimal Places property is set to Auto, the number of decimal places is determined by the Format property. If the Format is set to Fixed (or Standard), the number of places displayed is two. Access rounds the numbers accordingly.

Use The Decimal Places Property

Click the View button on the toolbar. Click in the next empty row of the Field Name column. TypeSingle_Fixed_FourDecimals. Press TAB, open the drop-down menu, and choose Number. Click in the Field Size property, open the drop-down menu, and choose Single. Press TAB, open the drop-down menu, and choose Fixed. Click in the Decimal Places property. Open the drop-down menu and select 4.

Click Save and click the View button to return to Datasheet view. Double-click the right border of the Single_Fixed_FourDecimals column to see the entire field name. Click in the first row of the column. Type8.7789 and click in the same column in the row below. Access stores and displays 8.7789. Type8.77499and press ENTER. Access displays 8.7750 but stores 8.77499 (you’ll see that number if you click on 8.7750). Thus, if the Format is Fixed (or Standard), Access will display the number of decimal places you selected in the Decimal Places property.

Use The General Number Format

Click the View button to return to Design view. Click in the next empty row of the Field Name column. TypeSingle_GeneralNumber_Auto. Press TAB and select Number as the data type. Click in the Field Size property and select Single from the drop-down list. Click in the Format field and select General Number from the drop-down list. Leave the Decimal Places property as Auto. Click Save and then click the View button to return to Datasheet view. Double-click the right edge of the new field to see the entire column.

Type8.77 in the first row of the new field and then click in the same field in the row below. Access both stores and displays 8.77. Type8.77789and press ENTER. Access stores and displays the entire number. Thus, with the General Number format, Access displays the number as it is entered.

Click the View button to return to Design view. Click in the next empty row of the FieldName column. TypeSingle_GeneralNumber_DecimalPlaces. Press TAB and select Number. Click in the Field Size property below and select Single. Click in the Format property and select General Number. Click in the Decimal Places property and select 4 (see Figure 1). Click Save and click the View button to go to Datasheet view.

Double-click the right edge of the new field to see the entire field name. Click in the first row of the new field and type8.7774. Click in the same column in the row below. Access both saves and stores 8.7774. Type8.77789and press ENTER. Access both stores and displays 8.77789 (see Figure 2).

Why does Access display five decimal places, even though you set the Decimal Places property to 4? When you use the General Number format, the Decimal Places property is irrelevant- you get the number you entered. Save your changes, close the table, close the database, and exit Access