Currency and the number of decimal places

[John Baker]

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

 

[John W. Vinson]

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]

 

[John Baker]

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.

 

[John W. Vinson]

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]