Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to use Decimal Data Type in VBA code

Posted on 2004-04-26
6
Medium Priority
?
21,897 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 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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