Excel Formula to Fix the Decimal Size Warning

If your template is very old (released prior to 2008) and doesn't have all the field values correctly rounded to proper sizes, you might encounter a decimal size warning when clicking "Save To DB". This tutorial shows how to fix the problem.

The first way is to use the "Round" Excel formula to make sure the result of calculations fits into the field size definition in the Access database. For example, in the template at Tax Column (c5001), we use a "Round" formula to make sure the line total has up to two digits after the decimal point:

=ROUND(oknQuantity_1*oknPrice_1,2)

If you need to apply this change to your own template, follow the steps below.

1. Backup your template.
2. Open the template.
3. Switch to design mode by pushing down the "Design Mode" button on the ribbon tab.
4. Click the cell for which you want to modify the formula. The Excel formula bar shows the current formula applied to this cell, like this:

   =oknQuantity_1*oknPrice_1

5. Modify the above formula so that it looks like this:

   =ROUND(oknQuantity_1*oknPrice_1,2)

6. You may need to repeat the above step to modify all the formulas in all the line total cells.
7. You may also need to check the formulas of the tax cells to make sure they have up to two digits after the decimal point.
8. Once done, exit design mode by clicking the "Design Mode" button again.
9. Save the template.

The second way is to modify the field size definition in the underlying Access database. This is described in Customization of Standard Fields (c4108).

The template attached to this document is a revised version of the Tax Column (c5001). This new one has exactly the same fields as the original one, but with a slightly different form layout and design.