How to use Decimal Data Type in VBA code

I need to record Purchase Order line item quantities to 2 decimal places. In the past I've always used a double data type and forced rounding to 2 decimal places in my forms. I've been toying with the idea of changing the table column from a double to a decimal datatype with a scale of 2 so that I don't have to do any rounding in my forms and have the jet engine take care of it for me at the table level. Since VBA doesn't support a decimal data type, how do I handle these values in code and avoid rounding problems?

For example if I have some code that nees to add decimal data type values, do I read each value into a variant as follows:

dim var1 as variant
dim var2 as variant
dim varSum as variant
var1 = 1.15
var2 = 1.85
varSum = cdec(var1) + cdec(var2)

How does VBA handle rounding in the above style code?

Or do I convert each value to a double and do the rounding myself:

dim dbl1 as double
dim dbl2 as double
dim dblSum as double
Who is Participating?

your'e right - point taken. I didn't verify this.
But, I still would use double. Just calculate with this type and before writing it to the table do a rounding with a custom function. This should be the easiest way.


Steve BinkCommented:
In your first section, VBA will not round at all.  You have declared 3 variant-type variables.  varSum will equal 3.  The rest is formatting.  Even with the Decimal subtype, you still have 28 decimal places to work with, and you used no conversions which required a change in precision  Also, since the Decimal is really only a subtype, I would suggest not basing a field on it.  It is bound to lead to conversion errors.

Your other options are Currency and Double.  Currency uses 4 decimals for accuracy, and I believe it uses 4/5 rounding if you choose to format to less than that.  Regardless of which data type you choose, it sounds like your question is comparing internal Access rounding methods to your own specific rounding function.  That decision lies pretty much with you.  Access uses 4/5 rounding for the most part, though some conversions (like CInt) have a weirdness to it with .5 values.  With a custom function, you always know where the decimal is going to fall, so to speak.  If you want to store the rounded number only, I recommend the custom function.

To avoid the majority of rounding errors, do all your calculations with the raw data and round only the final result.
You can't declare variables as decimals.
You would have to declare them as variants and then
convert them with cdec() to decimals.
But that seems unnececssary.
Go ahead with doubles, you even don't need to round them yourself.
In your field declaration for the table declare them as double, and as format use standard number.
This will - accordingly to your locale systems settings - give you a number with two digits after the comma. You don't need to round yourself, access does this.


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rmkAuthor Commented:
Andy, my testing does not seem to match your comment about formatting. As far as I can tell the format property is strictly for display purposes. For example, if I define a column as double and use standard number or even .00 as the format property and enter a value of 1.234, the value 1.23 is displayed until I click in that column for that row and then the 1.234 is displayed. At this point my thought is that defining the column as decimal with a scale of 2 guarantees that I have properly rounded data in the table, Then I can still use doubles in my code and handle rounding as required.
ANy reason why you don't use the Currency data type?
rmkAuthor Commented:
Just gunshy I guess. This client has changed too many things from whole values, to 2 decimal places, then to 4 decimal places, and then to 5 decimal places which kills the currency data type.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.