Double datatype comparison in VBA

[sophannaly]

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 :

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

[Rabbit]

Double values are not exact. It will always be off by a small fraction. So your 1160.25 is probably 1160.2500000000 001 or 1160.2499999999 9999. In your scenario, what you can do is convert the final number to an integer so the fractional part drops off before comparing it.

[sophannaly]

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

[NeoPa]

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.

[sophannaly]

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

[NeoPa]

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.