Help with expression not rounding properly

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Access 2000. I have an expression in my query that 99% of the time will
give me the correct result, that is rounds up or down correctly. For
example, I am getting a result of .04 when I should be getting .05 The
actual number is .045 which should round up to .05 or .005 should round up
to .01 but I get .00. Here is the expression I am using as suggested from
an earlier posting. Help is appreciated, Thanks.

Round(-CInt(-[ExcMoisture]*[% In Kernels Rounded]),2)/100

Here are the two fields that are being referred to: ExcMoisture:
IIf([Moisture]>5,(-CInt(-[Moisture]*100)/100)-5,0) and
% In Kernels Rounded: IIf([Foreign Mat]<0.05,100-[% Edible Kern
Rounded],Int(([InedibleKernels]/[SampleSize])*10^4+0.5)/10^4*100)
 
Randy said:
Access 2000. I have an expression in my query that 99% of the time will
give me the correct result, that is rounds up or down correctly. For
example, I am getting a result of .04 when I should be getting .05 The
actual number is .045 which should round up to .05 or .005 should round up
to .01 but I get .00. Here is the expression I am using as suggested from
an earlier posting. Help is appreciated, Thanks.

Round(-CInt(-[ExcMoisture]*[% In Kernels Rounded]),2)/100

Here are the two fields that are being referred to: ExcMoisture:
IIf([Moisture]>5,(-CInt(-[Moisture]*100)/100)-5,0) and
% In Kernels Rounded: IIf([Foreign Mat]<0.05,100-[% Edible Kern
Rounded],Int(([InedibleKernels]/[SampleSize])*10^4+0.5)/10^4*100)
Click to expand...


The Round function (and any other time Access rounds a
number for display purposes) uses "Banker's Rounding". This
is considered to be more accurate over a randonselection of
number than the "always up" you are doing in your
calculations. Bankers rounding always rounds toward the
nearest even number in the previous place. E.g. .025
rounds to .02 and .015 also rounds to .02 If you insist
on using always up rounding, write your own function to
encapsulate all the details.
 
Randy said:
Access 2000. I have an expression in my query that 99% of the time will
give me the correct result, that is rounds up or down correctly. For
example, I am getting a result of .04 when I should be getting .05 The
actual number is .045 which should round up to .05 or .005 should round
up to .01 but I get .00. Here is the expression I am using as suggested
from an earlier posting. Help is appreciated, Thanks.

Round(-CInt(-[ExcMoisture]*[% In Kernels Rounded]),2)/100

Here are the two fields that are being referred to: ExcMoisture:
IIf([Moisture]>5,(-CInt(-[Moisture]*100)/100)-5,0) and
% In Kernels Rounded: IIf([Foreign Mat]<0.05,100-[% Edible Kern
Rounded],Int(([InedibleKernels]/[SampleSize])*10^4+0.5)/10^4*100)
Click to expand...

PMFBI

typical "round up":

Int(([ExcMoisture]*[% In Kernels Rounded]) / 0.01 + 0.5) * 0.01

how does it work...

-- move decimal point of product over 2 spaces
by dividing by .01

.0453 / .01 --> 04.53

-- add .5

04.53 + .5 = 05.03

-- get rid of everything to right of decimal point
using function Int() which does not change
the type (still "working with a float") so can
be working with numbers larger than an integer
can be.

Int(05.03) = 05

-- move decimal point back 2 spaces
by multiplying by .01

05 x .01 = 0.05


Note how you can change where this rounding
occurs by changing what you use to "move the
decimal point" (.01 in example above).
 
Back
Top