Set a number field with decimal places.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FL - 12 Jul 2007 22:06 GMT

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!

Signature

Reply to this Message

Jeff Boyce - 12 Jul 2007 22:57 GMT

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

>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
[quoted text clipped - 5 lines]
> number like 50.25 or 1.75 That's it. No dollar signs. Any
> suggestions? Thanks!

Reply to this Message

Joseph Meehan - 13 Jul 2007 00:58 GMT

> 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
[quoted text clipped - 3 lines]
> 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.

Signature

Joseph Meehan

Dia 's Muire duit

Reply to this Message

Jamie Collins - 16 Jul 2007 13:23 GMT

On Jul 13, 12:58 am, "Joseph Meehan" <sligoNoSPAM...@hotmail.com>
wrote:

> Setting Description Decimal precision Storage size
>
[quoted text clipped - 3 lines]
> 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.

--

Reply to this Message