Precision of numeric data stored in variant

[tuna]

How is numeric data stored in variant data types? It seems that numbers in variant data types don't respond well to high-precision calculations. I had the calculation (data types as in the names):

dblX = varY - dblZ

and I had a case when:

varY = 0
dblZ = 0.0000000121

But dblX returned 0, not -0.0000000121.

In fact, I did some testing in the immediate window and trucated a few of the first zeros. Only when I got to 0.000121 was dblZ set to -0.0001.

When I apply CDbl() to varY, things are fixed. It seems odd that variants can hold doubles just fine, but don't return as double unless forced.

Is this behaviour normal?


Thanks

 

[MarkK]

I can't confirm your result. This code yields the precision you didn't get when I run it under Access 2007 ...

Sub test()
   Dim v As Variant
   Dim d1 As Double
   Dim d2 As Double
   
   v = 0
   d1 = 0.0000000121
   d2 = v - d1
   Debug.Print d2
   
End Sub

This code prints ...

-0.0000000121

... to my immediate window. Are you certain your dblX and dblZ were explicitly Dimensioned as Double???

 

[Galaxiom]

Maximum precision requires the use of the Decimal datatype. These are held as scaled integers.
However while a Decimal datatype can be declared in an Access field it cannot be declared directly in VBA.

To use them in VBA Dim a Variant datatype and then set the value using the CDec function.

 

[boblarson]

The only reason I can gather that you are using the Variant datatype, instead of Decimal directly, is to handle nulls. So, don't do it. Use the NZ function to handle nulls and declare your datatype correctly.