Currency and Number data type

[Mary Ann]

I want to create a calculated field to set up a price
increase and am a bit confused whether Currency or Number
in my underlying table would give me a more accurate
result.

Access Help tells me:

"Use a Number field to store numeric data to be used for
mathematical calculations, except calculations that
involve money or that require a high degree of
accuracy......Use a Currency field to prevent rounding
off during calculations. A Currency field is accurate to
15 digits to the left of the decimal point and 4 digits
to the right"

But, the Single, Double and Decimal field sizes for the
Number data type are shown to have a 7, 15 or 28 decimal
precision. So that would seem to imply that they are
going to give a more accurate result than the Currency
data type which has only 4.

When I create the calculated field based on the Number
data type I do seem to get some weird results.

Can anyone shed any light on this or point me in the
right direction - I'd be most grateful.

 

[John Vinson]

But, the Single, Double and Decimal field sizes for the
Number data type are shown to have a 7, 15 or 28 decimal
precision. So that would seem to imply that they are
going to give a more accurate result than the Currency
data type which has only 4.

When I create the calculated field based on the Number
data type I do seem to get some weird results.

Single and Double numbers are what are called "Floating Point" - a
method that's been around since the 1950's for computer storage of
large numbers. The number is stored in two pieces - a "mantissa", a
binary fraction between 0.5 and 1; and an "exponent", the power of two
by which that number should be multiplied.

The advantage is that you can store enormous numbers - a Double can
range up to 10^308, a number too large to be useful even for
cosmologists (there are only some 10^40 elementary particles in the
entire universe!)

The disadvantage is that the mantissa IS AN APPROXIMATION. Just as the
fraction 1/7 cannot be displayed exactly as a decimal fraction - it's
an infinite repeating 0.142856142856142856... - the fraction 1/10
cannot be stored as a binary fraction. So you WILL get roundoff error;
if you add 0.3 + 0.3 + 0.4 you may NOT get 1.0, but instead get
something like 0.999999999999983.

A Currency datatype is, on the other hand, a huge scaled integer. It
has four and exactly four decimal places and NO roundoff error. You
still need to watch out for rounding because of the four places; even
if you're displaying only two, if you calculate (say) 6% sales tax on
$1.29 the result will be 0.0774. It will DISPLAY as $0.08 but if you
add a lot of such values together you'll be off a penny pretty
quickly, and off by serious money in a day's sales at a grocery store!
So do your rounding at the point of the calculation:

SalesTax = Round([Price] * 0.06, 2)

 

[Mary Ann]

Thank you John. At last I understand these weird results
a bit more.

Does this mean that if I am working with "normal" numbers
rather than numbers the size cosmologists need I would be
better to use Currency data type and format it as say
Standard?

If that is so, what do I do when I have created a
calculated field - I can't set a data type only a
format. Do calculated fields have data types and if so
what is it - is it floating decimal like Number? I
understand that I can round the calculation at the point
I create it but if I can't control its data type then if
I use it in subsequent calculations there may be the
rounding off errors again.

Hope you can help, I appreciate it very much.

Mary Ann

-----Original Message-----

But, the Single, Double and Decimal field sizes for the
Number data type are shown to have a 7, 15 or 28 decimal
precision. So that would seem to imply that they are
going to give a more accurate result than the Currency
data type which has only 4.

When I create the calculated field based on the Number
data type I do seem to get some weird results.

Single and Double numbers are what are called "Floating Point" - a
method that's been around since the 1950's for computer storage of
large numbers. The number is stored in two pieces - a "mantissa", a
binary fraction between 0.5 and 1; and an "exponent", the power of two
by which that number should be multiplied.

The advantage is that you can store enormous numbers - a Double can
range up to 10^308, a number too large to be useful even for
cosmologists (there are only some 10^40 elementary particles in the
entire universe!)

The disadvantage is that the mantissa IS AN APPROXIMATION. Just as the
fraction 1/7 cannot be displayed exactly as a decimal fraction - it's
an infinite repeating 0.142856142856142856... - the fraction 1/10
cannot be stored as a binary fraction. So you WILL get roundoff error;
if you add 0.3 + 0.3 + 0.4 you may NOT get 1.0, but instead get
something like 0.999999999999983.

A Currency datatype is, on the other hand, a huge scaled integer. It
has four and exactly four decimal places and NO roundoff error. You
still need to watch out for rounding because of the four places; even
if you're displaying only two, if you calculate (say) 6% sales tax on
$1.29 the result will be 0.0774. It will DISPLAY as $0.08 but if you
add a lot of such values together you'll be off a penny pretty
quickly, and off by serious money in a day's sales at a grocery store!
So do your rounding at the point of the calculation:

SalesTax = Round([Price] * 0.06, 2)


.

 

[John Vinson]

Thank you John. At last I understand these weird results
a bit more.

Does this mean that if I am working with "normal" numbers
rather than numbers the size cosmologists need I would be
better to use Currency data type and format it as say
Standard?

It depends on what you need. If four decimal places are enough, you
can use Currency. If you need five or more, you've got no choice -
Double or Float.

If that is so, what do I do when I have created a
calculated field - I can't set a data type only a
format. Do calculated fields have data types and if so
what is it - is it floating decimal like Number?

It depends on the calculation. Addition and multiplication, I'm pretty
sure, will use the "heaviest" datatype that goes into the calculation;
that is, an Integer plus a Float plus a Double will give a Double
result. However, division will always give a Double.

What you can do is to wrap the expression in a type-conversion
function: e.g. CCur([a] / ) will divide a by b, giving a Double
result, but that will then be converted to Currency datatype.

 

[Mary Ann]

Wow. Thank you. I'll try the converting function! I
feel I have increased my understanding by masses!
Mary Ann

-----Original Message-----

Thank you John. At last I understand these weird results
a bit more.

Does this mean that if I am working with "normal" numbers
rather than numbers the size cosmologists need I would be
better to use Currency data type and format it as say
Standard?

It depends on what you need. If four decimal places are enough, you
can use Currency. If you need five or more, you've got no choice -
Double or Float.

If that is so, what do I do when I have created a
calculated field - I can't set a data type only a
format. Do calculated fields have data types and if so
what is it - is it floating decimal like Number?

It depends on the calculation. Addition and multiplication, I'm pretty
sure, will use the "heaviest" datatype that goes into the calculation;
that is, an Integer plus a Float plus a Double will give a Double
result. However, division will always give a Double.

What you can do is to wrap the expression in a type- conversion
function: e.g. CCur([a] / ) will divide a by b, giving a Double
result, but that will then be converted to Currency datatype.



.

 

[Mary Ann]

I know I have only just replied to your very helpful
answer but having read it again I realise I still have a
couple of points I would like to sort out. I know you've
been very helpful so far but would really appreciate just
a little more!

You refer to Float data type - this has confused me
because from your earlier answer I understood that Single
and Double used the "floating decimal point". What is
Float - is it the same as the Decimal option when looking
at Number data type size?

You also say you have to use Double or Float if you want
more than 4 decimal places - could you not use Single too?

What is the full "heavy sequence"? Is it Byte, Integer,
Long Integer, Single, Double, Decimal?

In hope that you have time and patience to reply!

Mary Ann

-----Original Message-----

Thank you John. At last I understand these weird results
a bit more.

Does this mean that if I am working with "normal" numbers
rather than numbers the size cosmologists need I would be
better to use Currency data type and format it as say
Standard?

It depends on what you need. If four decimal places are enough, you
can use Currency. If you need five or more, you've got no choice -
Double or Float.

If that is so, what do I do when I have created a
calculated field - I can't set a data type only a
format. Do calculated fields have data types and if so
what is it - is it floating decimal like Number?

It depends on the calculation. Addition and multiplication, I'm pretty
sure, will use the "heaviest" datatype that goes into the calculation;
that is, an Integer plus a Float plus a Double will give a Double
result. However, division will always give a Double.

What you can do is to wrap the expression in a type- conversion
function: e.g. CCur([a] / ) will divide a by b, giving a Double
result, but that will then be converted to Currency datatype.



.

 

[John Vinson]

I know I have only just replied to your very helpful
answer but having read it again I realise I still have a
couple of points I would like to sort out. I know you've
been very helpful so far but would really appreciate just
a little more!

You refer to Float data type - this has confused me
because from your earlier answer I understood that Single
and Double used the "floating decimal point". What is
Float - is it the same as the Decimal option when looking
at Number data type size?

Just longstanding habit on my part causing me to post in a confusing
way. Where I said Float I was referring to Single (because back in
1967 they called them Floats not Singles).

The Decimal datatype has just recently been added to Access; I've not
used it at all, because I've seen a LOT of comments that it's buggy
and unreliable. If it can be made to work (and it works very well in
SQL Server, so it should be possible!) it's going to be the datatype
of choice because it does not have roundoff error nor does it have a
rigid four-decimals limitation.

You also say you have to use Double or Float if you want
more than 4 decimal places - could you not use Single too?

Double or Single, exactly.

What is the full "heavy sequence"? Is it Byte, Integer,
Long Integer, Single, Double, Decimal?

I'm not sure just how the optimizer decides, but I'd guess that you're
correct.

 

[Mary Ann]

Many many thanks John - I think I've got it now!
Mary Ann