Solved

How to use Decimal Data Type in VBA code

Posted on 2004-04-26
6
21,292 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
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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA Access 2016 syntax 6 39
Access query expression 6 19
Display label on subreport when NO DATA on subreport 4 21
Filter a form 8 11
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

813 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now