Double vs Decimal?

[(PeteCresswell)]

This is a spin off from the thread
"Immediate Window: Annoying Notation?"

It's a *really* big deal to me all-of-a-sudden bc it sounds like
my current SOP might be at variance with good practice.

Lotta years ago (JET 3.something) and for reasons not recollected
I settled on Double as my data type of choice for big numbers
with lots of decimal places.

Now it's sounding like Decimal is the preferred vehicle, but I'm
a little confused - no... make that a *lot* confused. If
Decimal is so "right", how come VBA doesn't support it directly
in the Dim statement?

Decimal seems tb a slam-dunk for tables - where it's an explicit
data type, but for Dim'd values there appear tb issues that I
should resolve before I go and shoot myself in the foot.

Sounds like Decimal can be loaded into a Variant - either with a
simple equate as in "myVariant = !WhatEverDecimal" or by
coercion as in "myVariant = cDec(!WhatEverDecimal".


Is it a no-brainer to dim Variants instead of Doubles?
If so, what about the type checking that is sacrificed?


What about results where a computation operates on both Doubles
Decimals and the result rolls out into a Variant? Guaranteed
Decimal result? Does it matter?


Does anybody actually use all Variants for Dim'd fields where
decimals are necessary and there is no specific need for Double?

If so, do you always load the field by coercion - using cDec() -
or do you just do an equate?

Or do most people Dim Double for big numbers with lots of decimal
places?

 

[Tom van Stiphout]

On Sat, 26 Jan 2008 11:36:45 -0500, "(PeteCresswell)" <[email protected]>
wrote:

I exclusively use Double, but in my line of business it doesn't occur
all that often. One problem with double is that you have to be careful
comparing two of them. They may be different only in the nth decimal,
so you write a function to compare two to a certain level of accuracy.
If I understand Decimal correctly, comparisons can be made directly.
I would be reluctant to use Decimals in a VBA program because of the
weak type checking with variants.

-Tom.

 

[(PeteCresswell)]

Per Tom van Stiphout:

They may be different only in the nth decimal,
so you write a function to compare two to a certain level of accuracy.

Been there.... Can't recall how many decimal places out the
issues arose - but it was quite a few.... like more than 8.

Never occurred to me that it was the indefinite nature of Double.

 

[Allen Browne]

Pete, surely this depends on which type you need.

My default is to use Double rather than Decimal. The math executes faster,
and you don't need to worry too much about determining what scaling is being
used when you write generic (re-usable) functions.

You do need to be aware of the floating point accuracy issues:
http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems
That's actually quite simple.
For example, you don't loop like this:
Dim dblLoop as Double
For dblLoop = 0 to 1 step 1/7
Next
because VBA will interpret 1/7 as a double, and 7 times may not equal 1
exactly.

OTOH, Decimal has its problems too. VBA lacks a Decimal type, so you are
using Variant, with the accompanying overhead (as well as the slower CPU
execution.) JET does support Decimal, but is flawed:
http://allenbrowne.com/bug-06.html
Similarly, if you try to force a field to Decimal by typing this into the
Field row in query design, it fails:
CDec(3)

So, my suggestions would be:
a) Use Double where appropriate, and learn how to handle floating point
numbers.
b) Use Currency where you need a fixed point number with no more than 4
decimal places.
c) Use Decimal for cases where you really need it, and learn where the bugs
are to avoid.

 

[(PeteCresswell)]

Per Allen Browne:

b) Use Currency where you need a fixed point number with no more than 4
decimal places.

I thought they retired "Currency" as a data type.

Can't cite my version of JET offhand, but the MS Access I'm using
is 2003 w/SP1.

The only numeric types in my "DataType" drop downs are:

Byte
Integer
Long Integer
Single
Double
Replication ID
Decimal

 

[Douglas J. Steele]

Currency's its own data type. You're looking at the field size choices for
the Number data type.

The Currency data type's still alive and well in Access 2007.

 

[Jamie Collins]

Decimal seems tb a slam-dunk for tables - where it's an explicit
data type, but for Dim'd values there appear tb issues that I
should resolve before I go and shoot myself in the foot.

Sounds like Decimal can be loaded into a Variant - either with a
simple equate as in "myVariant = !WhatEverDecimal" or by
coercion as in "myVariant = cDec(!WhatEverDecimal".

Is it a no-brainer to dim Variants instead of Doubles?

Assuming WhatEverDecimal is an object (such as an ADODB.Field...) that
is strongly-typed as Decimal (...and !WhatEverDecimal.Type = adNumeric
is true) then typing the assignment variable as Variant rather than
Double is indeed a no-brainer.

? rs.Fields("WhatEverDecimal").Value
1234567890.0987654321

? rs.Fields("WhatEverDecimal").Type = adNumeric
True

myVariant = rs.Fields("WhatEverDecimal").Value : ? myVariant,
TypeName(myVariant)
1234567890.0987654321 Decimal

myVariant = CDbl(rs.Fields("WhatEverDecimal").Value) : ? myVariant,
TypeName(myVariant)
1234567890.09877 Double

Jamie.

--

 

[Jamie Collins]

my suggestions would be:
a) Use Double where appropriate, and learn how to handle floating point
numbers.
b) Use Currency where you need a fixed point number with no more than 4
decimal places.
c) Use Decimal for cases where you really need it, and learn where the bugs
are to avoid.

To the OP: this seems to be a sensible set of criteria to follow
(though in my experience I've only rarely encountered a scenario where
Double is most appropriate).

OTOH, Decimal has its problems too. VBA lacks a Decimal type, so you are
using Variant, with the accompanying overhead (as well as the slower CPU
execution.) JET does support Decimal, but is flawed:
http://allenbrowne.com/bug-06.html

To Allen, I think you meant:

http://allenbrowne.com/bug-08.html

I may disagree with much of its contents (I strongly disagreed "Nulls
and zeros sort unpredictably" but that bug's now fixed, of course) but
I wouldn't want you to miss your opportunity to state your case

Jamie.

--