By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,968 Members | 1,574 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,968 IT Pros & Developers. It's quick & easy.

Double datatype comparison in VBA

P: 67
Hi,

I have one issue that I don't understand. In my table I have fields like total, jan, feb, mar, apr, may,...,dec and all these fields I set its datatype as double and precision as auto.

In my form, I have use before update action to each input text box with condition as :

Expand|Select|Wrap|Line Numbers
  1. if(nz(me.jan,0)+nz(me.feb,0)+nz(me.mar,0)+nz(me.apr,0)+nz(me.may,0)+nz(me.jun,0)+nz(me.jul,0)+nz(me.aug,0)+nz(me.sep,0)+nz(me.oct,0)+nz(me.nov,0)+nz(me.dec,0)) >me.total then msgbox "exceed total" 
when I insert with these data :
total=15080
jan=feb=mar=0
apr=1072.96
may=2602.958
jun=2628.958
jul=2504.958
aug=2504.958
sep=2505.958
oct=1160.25
nov=99

it suddenly popup message "exceed total" when I input 99 to nov text box. But when I use msgbox to popup each value and its sum, it is 15080.

Could anybody tell me how to compare double datatype ?

Sophanna
Mar 30 '14 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,430
Double values are not exact. It will always be off by a small fraction. So your 1160.25 is probably 1160.2500000000001 or 1160.24999999999999. In your scenario, what you can do is convert the final number to an integer so the fractional part drops off before comparing it.
Mar 30 '14 #2

P: 67
Hi Rabbit,

So it means that I should use Round function by giving it exact precision digit before comparing these two values. Is it?

Sophanna
Mar 31 '14 #3

NeoPa
Expert Mod 15k+
P: 31,769
If you want to avoid rounding errors then you will need to round each element of the calculation to the number of decimals you require (probably 2). Alternatively, of course, use Currency or Number/Decimal for the fields in the first place to avoid such problems.
Mar 31 '14 #4

P: 67
Hi NeoPa,

So may I ask that to store with such these data 342.45323, 43232343,234 Number/Decimal datatype is much better than double type. Is it?

Sophanna
Apr 2 '14 #5

NeoPa
Expert Mod 15k+
P: 31,769
That rather depends on how you want to use it Sophanna. It seems you want to sum them together without rounding errors. That is certainly better with Number/Decimal.

Double is better when you have enormously large numbers that won't fit within a Number/Decimal, but frankly that's rare when dealing with currency values and calculations.
Apr 2 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.