Reverse Rounding of Decimals

Mark Nelson

unread,

Jun 18, 2002, 10:50:03 AM6/18/02

to

I have a Make-Table query in Access 97 that creates a
temporary table for manipulation by a form. The query
uses a summation expression that alters the data to give
more decimal places than exist in the original data. Data
that originally only has 1 decimal place will be given
many more places which are inaccurate. This occurs for
any data with 1 or more decimals other than .5.
Examples:
1 -> 1
1.2 -> 1.20000004768372
1.5 -> 1.5
5 -> 5
5.5 -> 5.5
5.6 -> 5.59999990463257
5.67 -> 5.67000007629395
5.678 -> 5.67799997329712
The data in the originating table is updated and stored
correctly when updated from the temp table with these
altered values, but the values are being generated by the
summation in the query and displayed in the form.

Please help,
Mark Nelson

Ken Head

unread,

Jun 18, 2002, 11:35:40 AM6/18/02

to

Mark, on your Form try setting the Format property for the field to
fixed or standard and the decimal places to 1 or 2 as desired.

Allen Browne

unread,

Jun 18, 2002, 11:39:25 AM6/18/02

to

As you may be aware, most fractional values cannot be stored acurately in
floating point numbers. The simplest solution may be to use a fixed point
number.

If you need no more than 4 decimal places, use a Currency type field in
your temp table, regardless of whether the numbers have to do with dollars
or not. Currency is a fixed point number scaled to 4 decimal places. Set
the Format property to display as a "General Number" rather "Currency". Set
the Decimal Places to 4, and it will always display 1.2 as 1.2000 (provided
the calculation was correct in the first place).

If not using a temp table, you can achieve the same in a query by forcing
the result through CCur(), e.g. to mark up a charge by 10%:
Amount: CCur(1.1 * Nz([Charge],0)

--
Allen Browne - Microsoft MVP (Most Valuable Professional)
Perth, Western Australia.
Allen Browne's Database And Training.
Tips for MS Access users: http://users.bigpond.net.au/abrowne1
Reply to the newsgroup. (Email address has spurious "_SpamTrap")

"Mark Nelson" <Mark....@honeywell.com> wrote in message
news:df5601c216d7$6dbfc7e0$a5e62ecf@tkmsftngxa07...

Mark Nelson

unread,

Jun 19, 2002, 9:51:34 AM6/19/02

to

I changed both the Make Table query and the resulting Form
to display only 2 decimal places. At first glance, this
solved the problem because only 2 decimal places are
displayed by the table from the query, the datasheet
behind the form and in the form. However, once these
truncated values are clicked on for editing, the same
occurrence of extra bogus decimal places results. The
truncated value expands to show all the extraneous decimal
places rather than the true 2-decimal-place value.
This is not how I'd like the end users to have to access
the data.

Thanks,
Mark

Allen Browne

unread,

Jun 19, 2002, 10:57:51 AM6/19/02

to

http://www.mvps.org/access/modules/mdl0054.htm

--
Allen Browne - Microsoft MVP (Most Valuable Professional)
Perth, Western Australia.
Allen Browne's Database And Training.
Tips for MS Access users: http://users.bigpond.net.au/abrowne1
Reply to the newsgroup. (Email address has spurious "_SpamTrap")

"Mark Nelson" <Mark....@honeywell.com> wrote in message

news:fe6301c21798$6c70b9f0$9ae62ecf@tkmsftngxa02...