How to eliminate trailing multiplication zeros?

[Guest]

In Access 2003, but not earlier editions, multiplication of a currency
field overrides the text box control's format property, so that 5.00 + 5.00
displays as 10.00 but 5.00 * 2 displays as 10.000000 - even if the currency
fomat is Fixed, 2 decimals. It appears to be a bug. Must I change the data
type to avoid the additional trailing zeros?

 

[Michel Walsh]

Hi,


A format is about the representation supplied to the end user, NOT the
way it is stored, internally. A format fixed to 2 decimal will work as well
and in the same manner against a decimal number, a currency, or even a
simple float.

A Currency is a decimal with a fixed number of digits (base 10) of 4.

Storing your data in cents (integer) rather than in dollar (currency) is
a possible way to solve the problem, but you have to remember it is cents,
not dollars. Using a decimal data type (more recent Access/Jet versions) is
another alternative.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks, Michel. I know the format is about the representation, not the data
type, but as I said in this case the data type _overrides_ the format, so the
trailing zeros display regardless of how I format the control. I perhaps
should have said I'm working in an adp, with SQL Server 2000 as my back end.
Anyhow, I've come to a workable (if not complete) understanding and remedy,
and - as I suspected and as you affirmed implicitly - the answer is yes, I
must change my data type.
Suspecting that the problem was the data type being displayed, I changed
from calculating via view to calculating in the table itself, in an
additional field. I created the new field as a money data type, but when I
entered a formula to do the multiplication and then saved it, SQL Server
converted the data type of the field (without generating an error message) to
a scale of 27 and a precision of 4, and it is this data type (unnamed, in the
table's properties section) that resists formatting. To be completely clear,
as soon as I entered a multiplication formula, SQL Server permanently changed
the data type of the field. (This table conversion didn't happen with
addition or subtraction.) My supposition now is that the view was making the
same data type conversion implicitly, so the field in the view was this
unnamed, unformatable data type.
Since neither a view nor a calculated field of the kind I've described
would work for me, I changed the data type of my new, additional field back
to currency and created a trigger on the table to do the multiplication on
insert and update, and this strategy succeeded. I also remedied a later, more
complex view by casting the product's data type as money.
Thanks again.