calculated column problem

  • Thread starter Thread starter agarwalp
  • Start date Start date
A

agarwalp

Hi

I have a query which has many of its columns calculated.Lets say

Col1=30002
Col2=1/some column -> value=0.130909
col3=col1*col2

Now if from above col3 will give 3927.53 when rounded will give 3928.
But 0.1309*30002 will give 3927.26 when rounded will give 3927.

And here lies my problem. I want 3927. But even after limiting the
decimal places of col2 to 4 (which gives 0.1309) the answer in col3
still comes out 3928. It seems access does not the rounded value.
Appreciate your help
 
Limiting the number of decimal places limits the number displayed, not the
number stored. So, the entire number is still there for the calculation. You
will need to round the number yourself before using it in the calculation.

Example:
Col3 = Col1 * MyRound(Col2)

MyRound would be a "user defined function" that you create in a standard
module. Access 2000 and newer have a Round function available, but it does
"banker's rounding" where .5 will always round to an even number, not up
each time as most folks are used to. If that is acceptable, you could use
the built in function.

Rounding to 4 decimals where .5 always rounds up:
Public Function MyRound(dblRoundMe As Double) As Double
MyRound = Int((dblRoundMe * 10 ^ 4) + 0.5) / (10 ^ 4)
End Function

By using the exponent, you can see where to change this if you also wanted
to pass the number of decimals to the function.
 
What data types are these fields?

Open the Immediate window, and enter:
? CDbl(0.130909) * 30002
This yields:
3927.531818

Now try:
? CCur(0.130909) * 30002
That yields:
3927.2618

The Double type has around 15 significant figures, so the calculation yields
a result that rounds upwards. The Currency type has only 4 decimal places,
so the last 2 digits are lost *before* the multiplication takes place, and
the end result rounds downwards.

If you *always* want the result rounded downward, use Int().

If you want to control how the values are understood in the calculated
control, use the typecasting functions.

More info in this article:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html
 
Back
Top