Data Types

[Ben]

I have a two part question regarding numeric data types:

1. Is there any way to preserve the data in an existing field when converting the field's data type from Single to Double? The data always ends up changed to different decimal places and amounts.

2. We are storing our dollar amounts and weights mainly in double data type fields. Is this the best data type to use? Storing it as a single causes decimal places to be lost (Why?) so this is not an option.

Thanks in advance for any help.

Ben

 

[Seth Schwarm]

On question 1 I don't have enough information to shoot
you an answer. What is your application that is causing
you problems?

On question 2.

To deal with money, always use the currency data type.
It is built to handle money and help control it from
getting out of control with decimal issues. Otherwise,
when you are working in your database, you will always
have to build in compensating factors when doing
calculations with fields that actually store currency.

You should use Single when you need to store numeric
values with up to 7 decimal places of accuracy and Double
when you need to store up to 15 decimal places of
accuracy.

Take a little time and type in each of these Keywords
into Access' Help screen on the Index tab: DataType; and
then FieldSize;

These two help screens will give you great guidance on
how to properly apply data types and field sizes
according to what actual data you plan to store.

Seth Schwarm

-----Original Message-----
I have a two part question regarding numeric data types:

1. Is there any way to preserve the data in an existing

field when converting the field's data type from Single
to Double? The data always ends up changed to different
decimal places and amounts.

2. We are storing our dollar amounts and weights mainly

in double data type fields. Is this the best data type
to use? Storing it as a single causes decimal places to
be lost (Why?) so this is not an option.

 

[John Vinson]

I have a two part question regarding numeric data types:

1. Is there any way to preserve the data in an existing field when converting the field's data type from Single to Double? The data always ends up changed to different decimal places and amounts.

2. We are storing our dollar amounts and weights mainly in double data type fields. Is this the best data type to use? Storing it as a single causes decimal places to be lost (Why?) so this is not an option.

A Single is a 32-bit binary approximation, good for about 7 decimal
places, with roundoff error in the last of those seven; Double is a
64-bit binary, with roundoff error in the 13th or 14th decimal place.

Currency (not the currency format, but the currency datatype) is a
huge scaled integer, with a range into the trillions and *exactly*
four decimal places. As such it does NOT have roundoff error; the
price you pay is the limitation to just four decimals, no more, no
fewer.

 

[Guest]

Ben:

Question 1

This just doesn't sound right. In the past I have made
fields be Integer and later decided Long Integer would be
best. After updating the Field Size in the table design
view, my data suffered not damage. Now granted integer
and decimal numbers are worlds apart as far as
functionality. I am sorry, but I have never changed a
data type from Single to Double in a database before, to
experience the results firsthand.

I am sure you have heard it alot; databases are all about
design, design, design. I have made my share of mistakes
in the past, so I feel your pain.

Question 2

If you are using it for money; dollars and cents,
absolutely not. If you are getting into fractions of a
penny calculations such as interest calcs, you may be
better served to look into using Single or Double
depending on accuracy.

I write Access applications for a living and use Currency
all the time. It works for me and my clients.

Always remember, never substitute someone else's judgment
for your own. If you have a gut feeling, go with it.

Seth

-----Original Message-----
Thank you for responding.

In regards to question 1, I meant that we had initially

created our Access database using Single data types for
numeric fields with decimal places and later realized
that we needed to use Doubles. So we changed the data
type in Access for those fields to Doubles and this
screwed up the data in the existing records, rounding
numbers, adding decimal places, etc... Any way to avoid
this?

 

[Ben]

Thank you both for your help.