Set a number field with decimal places.

[Guest]

I need to change a number field to accept 2 decimal places. However, my
existing field is a number field with data type Long Integer. Do I need to
change this to single type field with a currency or fixed format? Or, some
other data type field? The number is not currency but MS help suggests
currency for decimal places. Not sure why. I need to display and store a
number like 50.25 or 1.75 That's it. No dollar signs. Any
suggestions? Thanks!

 

[Jeff Boyce]

Change the LongInt field to Currency. That data type will store up to 4
decimal places, and will only show the $ if you set the format also to
Currency.

If you used the Single or Double data type, you could get rounding errors
(1.0 + 1.0 = 1.9999999999!) when you do math on the values. If you use
Currency (with no more than 4 places), no storage/rounding errors.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joseph Meehan]

I need to change a number field to accept 2 decimal places. However,
my existing field is a number field with data type Long Integer. Do
I need to change this to single type field with a currency or fixed
format? Or, some other data type field? The number is not currency
but MS help suggests currency for decimal places. Not sure why. I
need to display and store a number like 50.25 or 1.75 That's
it. No dollar signs. Any suggestions? Thanks!

If the DataType property is set to Number, the FieldSize property settings
and their values are related in the following way.

Setting Description Decimal precision Storage size
Byte Stores numbers from 0 to 255 (no fractions). None 1 byte
Decimal Stores numbers from -10^38-1 through 10^38-1 (.adp)
Stores numbers from -10^28-1 through 10^28-1 (.mdb) 28 12bytes
Integer Stores numbers from -32,768 to 32,767 (no fractions). None 2
bytes
Long Integer (Default) Stores numbers from -2,147,483,648 to
2,147,483,647 (no fractions). None 4 bytes
Single Stores numbers from
-3.402823E38 to -1.401298E-45
for negative values and from
1.401298E-45 to 3.402823E38 for positive values. 7 4 bytes
Double Stores numbers from
-1.79769313486231E308 to
-4.94065645841247E-324
for negative values and from
4.94065645841247E-324 to
1.79769313486231E308 for positive values.

 

[Jamie Collins]

Setting Description Decimal precision Storage size

Decimal
Stores numbers from -10^28-1 through 10^28-1 (.mdb) 28 12bytes

Long Integer (Default) Stores numbers from -2,147,483,648 to
2,147,483,647 (no fractions). None 4 bytes

Default for what? e.g.

SELECT TYPENAME(0.1)
FROM AnyTableHere;

returns 'Decimal' because the DECIMAL type is Jet's native type for
'decimal' values (i.e. where the decimal portion is non-zero and the
scale/precision is within the limits for the DECIMAL type), surely a
candidate for consideration of the 'default' type for decimal numeric
values?

Jamie.

--