Round function not working in query

[Ivan Debono]

Hi all,

I have a query that lists records out of table. A field contains a double
value with 0 to 9 decimal places. A second field contains the number of
decimal places the value has to be rounded to.

So the query looks something like this:
select round(myvalue, mydecplaces) as value from mytable

Strange enough, the myvalue is not being round.

Is it a bug or what?

Thanks,
Ivan

 

[giorgio rancati]

Hi Ivan,
what rounding type you want ?
The Access round function provide Banker's Rounding
If you want a symmetric arithmetic rounding (i.e. Sql Server round()
function )
put this code in a bas module
----
Function MyRound( _
number As Variant, _
decplaces As Variant) As Double

If IsNull(number) or IsNull(number) Then Exit Function

MyRound = Fix(Cdec(number) * 10 ^ decplaces + _
(0.5 * Sgn(number))) / 10 ^ decplaces

End Function

 

[Tom Ellison]

Dear Ivan:

May I suggest you temporarily change this query as follows:

select myvalue, mydecplaces, round(myvalue, mydecplaces) as value from
mytable

Then you can see the values on which the query is operating. If any of
these results seem incorrect, post them back here so we can see what you're
seeing.

Tom Ellison

 

[Ivan Debono]

This is weird, have a look at the following. OrigValue is 99% correct
because the decimal places are set in my application. But the NewValue using
the round function is way off.

Note: I can only use standard sql functions and no code in bas modules
because the queries are called from a vb app.

Ivan
OrigValue DecPlaces NewValue
31.16 2 31.1599998474121
6.47 2 6.46999979019165
87 2 87
0.4862185 2 0.490000009536743
10.2 1 10.1999998092651
1550 0 1550
484 0 484
17 0 17
2.7 3 2.70000004768372
2.423 3 2.42300009727478
0.846 3 0.846000015735626

 

[Tom Ellison]

Dear Ivan:

Actually, this is very revealing, as you have already seen. And the
solution is actually quite straight forward, though it seems mysterious at
the moment.

Your values seem to be of a datatype that is either "single" or "double".
These are forms used for scientific measurements. They should never be used
for accounting values.

Such values are not capable of exact representation of every decimal number.
They have great strength when used in science and engineering, but are
probably not acceptable for your purposes. Although I am an engineer by
training and have done a lot of such work, including computer programming,
I've never used any of these in a database. There are datatypes in
databases that ARE capable of representing your numbers exactly, and you
should stick with them. This includes the Currency or Money datatype, which
might be just what you would want here.

On the other hand, if you display the values you have on a form or report
with a specific number of decimal places, they should round to the values
you are expecting. 31.1599998474121 will display as 31.16 at 2 places,
31.160 at 3 places, and 31.1600 at 4 places. But when the values are added
in a long column, it might be possible that the tiny errors will accumulate,
and you'll get a sum that is not accountingly correct.

The moral is, don't use floating point (Single or Double) to represent
accounting values. Sorry if this creates a lot of work for you. In my
opinion, there isn't sufficient warning against this practice in the help
files and in some of the books on the subject I've seen.

Tom Ellison

 

[Ivan Debono]

A very explanative answer! Thanks!

Unfortunately I can't change the datatypes at this stage. The values need to
be rounded in reports, so I'll have to 'hardcode' the rounding there then!!

Thanks,
Ivan

 

[Tom Ellison]

Dear Ivan:

Before you go to all that work, please try just specifying in the format how
you want the values displayed. When formatting to a specified number of
decimal places, rounding will occur.

There will still be a potential problem with the sum possibly being not the
correct value due to the rounding. If you were to convert the values to
currency type with the CCur() function, then round the values, both these
steps being done in the query, the problem would almost certainly disappear.

Tom Ellison