double vs. decimal

[Jennifer]

In Field Size, what is the difference btwn Double and Decimal? I want data
entered to have 2 decimal places and NOT be rounded up or down. So, if
someone enters 721.999, it will go in as 721.99. Does Decimal work for that?
I have seen several people recommend using Currency. Can someone tell me
the difference btwn Currency and Decimal in this case? Thanks.

 

[Wayne Morgan]

Currency holds 4 decimal places, but normally will display 2 of them (US
format). If you only want two decimals stored, then in the AfterUpdate event
of the control on the form, use code to change the value to what you desire.

The main problem with the decimal data type is that it isn't fully
supported. See this note from the help file:

Note At this time the Decimal data type can only be used within a Variant,
that is, you cannot declare a variable to be of type Decimal. You can,
however, create a Variant whose subtype is Decimal using the CDec function.

The most accurate data type will be Currency. You can use a Currency data
type without formatting the field as currency.

To truncate everything after the second decimal place:
curX = Fix(curX * 100) / 100

The difference between Fix() and Int() is in how negative numbers are
handled. Int() will give you the nearest integer lower than the current
number and Fix() will just truncate the decimal.

Example:
Int(1.222) >>> 1
Int(-1.222) >>> -2
Fix(1.222) >>> 1
Fix(-1.222) >>> -1

 

[aaron.kempf]

uh.. if you really wanted to do you could store everything as ints by
multiplying everything times 100

i like ints a lot better than those crazy decimals

-aaron

 

[Jennifer]

Thanks for the info. Unfortunately, I am not yet proficient enough to be
writing codes. If I were to use Currency in the field properties and set it
up properly, could I get two decimal places with no rounding (so 721.999
will go in as 721.99)? I have read about Decimal not being fully supported
and people having some problems with it. Have you heard of any issues with
the data going from forms to tables or exporting to another data package or
does it just seem to be with the sorting? Thanks so much!

 

[Albert D.Kallal]

yes, just use the currency data type.

As for people only being allowed to enter 2 decimal places, use a import
mask. Input masks allow you to force input and data formats to ANY way you
want.


So, if you only want to allow users to enter up to 2 decimal places, use a
type of currency, and then setup a input mask

##########.##

The above will NOT allow the user to enter more then 2 digits after the
decimal point. You can use the above input mask for any type of numbers, but
I would use currency in your case....

To remove the $ during data entry, just set the format to fixed, and set the
decimal places to 2

With the above, you don't need any code or anything.

 

[Jennifer]

I just tried this with the following:
Field size: Double
Format: Currency
Decimal places: 2
Input mask: ###.##

When I enter 333.688 into the form, it shows up as 333.688 in the form and
$333.69 in the table. So, it is still rounding up and allowing people to
enter more than 2 decimal places. I am sure I am doing something wrong. Can
you give me any suggestions?

 

[Joan Wild]

I just tried this with the following:
Field size: Double
Format: Currency
Decimal places: 2
Input mask: ###.##

In the upper pane of the table design, choose Currency as the Data Type, not
number. There will be no 'Field size'. Choose Format of Fixed (currency
format will display a '$' which you may not want); choose two decimal
places. Keep your input mask as it is.

Note that your form will not automatically inherit these changes after the
fact. Just delete the control on the form, and re-add the field to the
form.

 

[Jennifer]

Thanks for the info. Now I understand where the currency format needs to be,
but I am still having a rounding problem. I set the format to fixed with 2
decimals, but it continues to allow entry of more than 2 decimals and then
rounds to 2 in the table. Is there a way to ensure that people can only
enter 2 decimal places and there will be no rounding? Can I do this with
Currency or should I consider using Decimal? Thanks again.

 

[Joan Wild]

Thanks for the info. Now I understand where the currency format needs to
be,
but I am still having a rounding problem. I set the format to fixed with
2
decimals, but it continues to allow entry of more than 2 decimals and then
rounds to 2 in the table. Is there a way to ensure that people can only
enter 2 decimal places and there will be no rounding? Can I do this with
Currency or should I consider using Decimal? Thanks again.

The input mask should prevent the entry of more than 2 decimals. Again
ensure that the input mask exists on the control on your *form* that is
bound to this field.

 

[Albert D.Kallal]

I just tried this with the following:
Field size: Double
Format: Currency
Decimal places: 2
Input mask: ###.##

YOU MUST set the input mask on the control on the actual form, and NOT in
the table definition. For sure, you can leave the above, since each NEW
control you place on a form will grab/use the above input mask. So, just
remember, the input mask is set in the control, and if you go and change the
setting in the table, EXISTING controls on screens and forms will NOT
change. In effect, the table input mask is really just a default that gets
used for each control you place on a form.

Try the above input mask, it will work. Again, you MUST make this setting on
the control you place on the form.

You don't need a bunch of silly code and other junk, simply use the above.
The above will work, and above will ONLY allow a user to enter 2 decimal
places.

 

[Jennifer]

Thanks!! I didn't understand the difference between setting a control on a
table vs. a form. It works now. Thanks so much!