Currency and the number of decimal places

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Baker - 30 May 2007 03:36 GMT

I have an application which has neen running for a number of years, firstly
on Access2000 then XP and now on Access 2003.

It uses many currency fields for invoicing , credit notes etc. Each field
which is of currency type has the decimal property set to 2.
Up until recently the ATB balanced fine but lately it has been a few cents
out. The reason is that some amounts have been put in with 3 decimal places
and some calculations (eg GST of 10% of $398.68 returning to the GST field
a value of $39.868) returning 3 decimal places.

I guess with the calculations, I can code to make sure only 2 decimal places
are returned, but is there a way to stop the entry of 3 decimal places in a
currency field? I would have thought that by limiting the property of the
field to 2 decimal places then only 2 places could be entered. Wrong!

I'm looking for a quick and easy way to make sure only 2 decimal places are
returned so that I don't have to go through all my forms and put in code for
every currency field.
Any ideas?

John B

Reply to this Message

John W. Vinson - 30 May 2007 03:56 GMT

>I guess with the calculations, I can code to make sure only 2 decimal places
>are returned, but is there a way to stop the entry of 3 decimal places in a
>currency field? I would have thought that by limiting the property of the
>field to 2 decimal places then only 2 places could be entered. Wrong!

Wrong indeed. A Currency datatype field always has four, and exactly four,
decimal places.

You need to Round or truncate the calculations to two decimals at the time the
calculation is done.

John W. Vinson [MVP]

Reply to this Message

John Baker - 30 May 2007 04:03 GMT

Thanks John.

You have just given me the go ahead to do what I was trying to avoid doing
but what I really need to do.

Cheers
John B.

>>I guess with the calculations, I can code to make sure only 2 decimal
>>places
[quoted text clipped - 11 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 30 May 2007 05:24 GMT

>Thanks John.
>
>You have just given me the go ahead to do what I was trying to avoid doing
>but what I really need to do.

Just note that the Decimal Places property of a currency (or Number, for that
matter) field only affects the appearance - what's shown, rather than what's
stored.

You can also use a Number... Decimal type and explicitly set the size of the
Decimal number to use two decimals, if you have A2003 or later.

John W. Vinson [MVP]

Reply to this Message