Averages & Decimal places

[JeRz653]

Hi everyone,

I am beyond frustrated with this because I feel like this is simple. Yet, I'm having issues with it. I am trying to use the average function on a field. The field calculates correctly in data sheet view but when I run a query, it displays with too many decimal places. The format I am looking to do is in a xx.xx result. I have tried changing the data type from decimal to double back to decimal, fixed format, and changed the decimal places all have resulted in output like 15.3444446 no matter what I do.

Right now, I have my data type as decimal with fixed formatting and 2 decimal places.

Any suggestions as to what I can do to correct this? Thanks!

 

[CJ_London]

in a query, it doesn't matter since it just hasn't been formatted - it only matters at the final destination form or report.

but if you want to show just the two (or whatever) decimal places you can do it in the format property for the query column as you would for a control on a form or report- right click and select properties

 

[Simon_MT]

The other way is to use CLng basically to convert number to an integer (* 100) and then restore the two decimal places. Beats Round which I hace seen just take the precision in the round statement leaving the remaining numbers beyond that precision.

CSng( CLng((Expression) * 100) / 100))

Simon

 

[Galaxiom]

Beats Round which I hace seen just take the precision in the round statement leaving the remaining numbers beyond that precision.

Can you post an expression to reproduce this?

I notice you are using Single in your example. Maybe that was the issue. If I remember correctly, Single is only good for a couple of decimal places. Double is better but there can still be anomalies after a few decimal places.

Using Int() on the results of Single or Double calculations can return big surprises calculating on numbers with just one decimal place.

If precision matters, calculations should be done in Currency (good for four places) or Decimal which can be configured to trade maximum supported size against decimal precision.

 

[Simon_MT]

You should use CLng as it is a larger and I don't think Lng was ever implemented. Int goes up to 32767 so if you are multiplying by 100 you are limited in the value you are applying Int to.

Simon

 

[Galaxiom]

You should use CLng as it is a larger and I don't think Lng was ever implemented. Int goes up to 32767 so if you are multiplying by 100 you are limited in the value you are applying Int to.

Yes but I think you have completely missed the point. I was trying to explain why you may have got unexpected results with Round().

Calculations with Double or Single are imprecise. For example calculating 6.3/0.7 with Single or Double variables does not return 9 but a value just short of 9. Applying Int() to that result will return 8.

If you Round() a Single you should not expect it to deal well with the digits beyond a couple of decimal places.

BTW Int() returns the Integer part of the parameter. That is, it drops the numerals after the decimal point. CInt() will return the nearest integer value in effect rounding it to zero places.

 

[Simon_MT]

I was was trying to point out that there are size limitation with Int

 

[Galaxiom]

I was was trying to point out that there are size limitation with Int

You are quite mistaken. Both the parameter and return value of Int() are Double.

Moreover, as I have already pointed out, neither CInt() nor CLng() is equivalent to Int() even when working within their maximum value limitations. CInt() and CLng() round the number to the nearest integer while Int() returns the largest integer that is less than or equal to the input parameter.

Now lets get back to the claim you made that Round() produces inconsistent results and your advice to use this instead:

CSng( CLng((Expression) * 100) / 100))

Do you understand the precision limitations of Single?

Can you provide an expression that demonstrates the claimed inconsistent performance of Round()?