Decimal Truncation

In MS Access, there may be situations, more rarely, when you don’t want to round some numerical values (for example, school averages), but truncate – remove some decimals. Since we don’t have a specific function, we can force the display from numeric value to text value.  Although it is possible to use functions directly (as in ex. 1) in a query, report, or form, it might be more advisable to create a VBA function, as in ex. 2.

Example 1 – Desired display via text functions (InStr, Left, Mid)

Using a VBA function makes certain refinements easier; for example, if we want all grades to have two decimals, even if it’s just a whole number, except grade 10.

Example 2 – VBA function


Author: Ovidiu.S

I am quite passionate about this professional area as if I know something - no matter how little - I want to share it with others.

Leave a Reply