459,968 Members | 1,574 Online
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 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
5 Replies

 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

 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

 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