Numbers won't Round (1 Viewer)

[GaelicFatboy]

Hi everyone,

I’m trying to construct a query for a report that calculates the internal volume of pipes in litres accurate to two decimal places, but when I view the query the volumes come back with 13 or so decimal places. I’m using a function to calculate the internal volume like so.

InternalVolume = Round(((Mysgl_Length * 0.25 * Pi * (Mysgl_ID / 1000) ^ 2) * 1000), 2)

I’ve set the field to a general number with two decimal places and even used the “Round” function within the query. The question is why won’t the numbers round down to the number of places I’m specifying?

Cheers

D

 

[gemma-the-husky]

not sure what number you are seeing, is it something very close to the correct number? if so, try this


in the query, right cllck the cell header, and try properties

set the field format to fixed, and 2 DPs. that should work.

if you put the field on a form, then you may need to do the same formatting

--------------
this might be the issue - real numbers are very hard to deal with.

the trouble is that most cannot be perfectly represented. so say your calculation results in a rounded value of 123.45

now it may be that computers cannot store the value of .45 perfectly - so it actully stores .4499999999762

this is why testing a real number for a specific value may not work, and instead you have ot test for a small delta difference.


eg - you calculate mynum as 123.45

and in your code you try

if mynum = 123.45 - and this reports false - because mynum is not precisely 123.45

you have to say stuff like

if abs(mynum - 123.45)<0.0001 then

-----
one way round this is to use currency format (instead of double or single) , which DOES represent numbers perfectly. Ignore the fact that it is called currency. But it stores numbers and handles numbers differently, but note also that the precision is different also.

an other choice would be to calculate the numbers as 100 times bigger than you want, and store them as whole numbers. Then there are no decimals to worrry about, although you have ot use a bit of jiggery-pokery to display the values.

 

[GaelicFatboy]

Cheers,

I had the properties set to general number, it's set to fixed at 2 places and things seem to be rounding nicely. I was trying to resolve the problem too much in the function by using long numbers etc. but I wanted a function that the whole database could call on and not a special one for the query.

Anyway all sorted now.

Thanks

D