Decimal Places

[GregInOz]

This may sound silly, but how can force a currency field to be only 2 decimal
places?

I've set the decimal places to 2 in the properties box, but i still get a
result of
$0.004

The field "balance" shows $0.00 but when i click on the field it shows the
above amount.

 

[Duane Hookom]

You could try to use a decimal type field with 2 decimal places. Setting a
property to "display" only 2 decimal places doesn't force a field to store
only 2 decimal places.

 

[GregInOz]

Hi Duane,

I've tried each of the different formats, but they all give the same answer
0.004, i just thought there maybe some way to ensure the field is strictly 2
decimal places not just in display.

I have a checkbox "paid" which via an event is made True if the the
"balance" field is equal to 0, however of course the event won't fire if we
have a balance of $0.004, even if the display shows $0.00

Greg

Greg

 

[Duane Hookom]

I didn't suggest you change anything regarding "different formats". My
suggestion was to go to the table design and change the field data type to
Decimal with 2 decimal places which is set using the Scale property.

 

[oldmdb]

Assuming the 2 fields were 'Invoice' and 'Payment', you can try this for the
'Balance' field
(Int([Invoice]*100)-(Int([Payment]]*100))/100

This gets rid of the 3rd decimal place (.004) in the equation.

 

[GregInOz]

Duane,

Sorry i misunderstood you instruction.

I did as instructed below, but no change.

Greg.

 

[Duane Hookom]

Are you referencing a value stored in a table in a field with the type
Decimal and Scale of 2? Or, are you referring to a calculation in a query?

 

[GregInOz]

I changed balance field in the table to Decimal and Scale of 2.

It's actually a calculation field on the form itself.

Greg.

 

[Duane Hookom]

If it's a calculation then it's not a stored value in a field. Changing
field properties won't do you any good if this value is a product or sum or
whatever.

Calculations can't be modified by the users so why allow users to "click on
the field"? I suppose you can multiple by 100 then integer divide by 100.
=([YourExpression] * 100) \ 100

 

[GregInOz]

It's been a learning exerience and thanks for your input. Changing the data
type to Decimal & Scale to 2 is something will be able to use in the future.

I ended up using the the post from Oldmdb below.

Thanks again

Greg.

If it's a calculation then it's not a stored value in a field. Changing
field properties won't do you any good if this value is a product or sum or
whatever.

Calculations can't be modified by the users so why allow users to "click on
the field"? I suppose you can multiple by 100 then integer divide by 100.
=([YourExpression] * 100) \ 100
--
Duane Hookom
MS Access MVP

I changed balance field in the table to Decimal and Scale of 2.

It's actually a calculation field on the form itself.

Greg.

 

[oldmdb via AccessMonster.com]

Glad to be of help

It's been a learning exerience and thanks for your input. Changing the data
type to Decimal & Scale to 2 is something will be able to use in the future.

I ended up using the the post from Oldmdb below.

Thanks again

Greg.

If it's a calculation then it's not a stored value in a field. Changing
field properties won't do you any good if this value is a product or sum or

[quoted text clipped - 69 lines]

 

[diana]

I'm trying to enter numbers without putting in a decimal - i.e, in 2003
excell, 2310 would show as 23.10, but without my putting in a decimal. any
suggestions?