Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to use Decimal Data Type in VBA code

Posted on 2004-04-26
6
Medium Priority
?
22,125 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 51

Assisted Solution

by:Steve Bink
Steve Bink earned 500 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

571 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