Solved

How to use Decimal Data Type in VBA code

Posted on 2004-04-26
6
21,455 Views
Last Modified: 2007-12-19
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
dblSum=MyRoundingFunction(dbl1+dbl2,2)
0
Comment
Question by:rmk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 125 total points
ID: 10920431
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.
0
 
LVL 7

Expert Comment

by:IT-Schubertz
ID: 10920433
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.

Greetings

Andy
0
 

Author Comment

by:rmk
ID: 10920569
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.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 11

Expert Comment

by:LambertHeenan
ID: 10921073
ANy reason why you don't use the Currency data type?
0
 

Author Comment

by:rmk
ID: 10921131
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.
0
 
LVL 7

Accepted Solution

by:
IT-Schubertz earned 125 total points
ID: 10921362
rmk,

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.

Greetings

Andy
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question