Cant round query result to nearest hundreth

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

Randy

I have a report based on a query with various calculations. Below is the
expression I am using, but it is not rounding to the hundreths. I have
tried many thing without success. I am trying to obtain the amount over 5
but rounded to .00 A field in my report has it's control source to
[ExcMoisture]

The query field below is named [ExcMoisture]

IIf([Moisture]>5,Round([Moisture],2)-5,"0") I keep geting this result:
3.705, I have to have a result of: 3.71, other calculations will be based
on 3.71 not 3.705.

Thanks for any ideas...Randy
 
Ken, I tried this expression, but I am getting 3.7 rather than 3.71
thanks..Randy

Ken Snell said:
IIf([Moisture]>5, (CLng([Moisture]*100)/100)-5, "0")

Any particular reason you're putting zero inside quotes to make it a string?
Seems unnecessary.

IIf([Moisture]>5, (CLng([Moisture]*100)/100)-5, 0)
--
Ken Snell
<MS ACCESS MVP>

Randy said:
I have a report based on a query with various calculations. Below is the
expression I am using, but it is not rounding to the hundreths. I have
tried many thing without success. I am trying to obtain the amount over 5
but rounded to .00 A field in my report has it's control source to
[ExcMoisture]

The query field below is named [ExcMoisture]

IIf([Moisture]>5,Round([Moisture],2)-5,"0") I keep geting this result:
3.705, I have to have a result of: 3.71, other calculations will be based
on 3.71 not 3.705.

Thanks for any ideas...Randy
Click to expand...
Click to expand...
 
Yes, that is because the CLng will round down when the last digit is even
and the digit being dropped is a 5:
3.705 goes to 3.70
3.715 goes to 3.72

If you want it to go up, you can use either of these expressions:
IIf([Moisture]>5, (-Int(-[Moisture]*100)/100)-5, 0)
(this will work so long as Moisture will not exceed 32,767)

IIf([Moisture]>5, (CLng(([Moisture]+0.0001)*100)/100)-5, 0)

--
Ken Snell
<MS ACCESS MVP>

Randy said:
Ken, I tried this expression, but I am getting 3.7 rather than 3.71
thanks..Randy

Ken Snell said:
IIf([Moisture]>5, (CLng([Moisture]*100)/100)-5, "0")

Any particular reason you're putting zero inside quotes to make it a string?
Seems unnecessary.

IIf([Moisture]>5, (CLng([Moisture]*100)/100)-5, 0)
--
Ken Snell
<MS ACCESS MVP>

Randy said:
I have a report based on a query with various calculations. Below is the
expression I am using, but it is not rounding to the hundreths. I have
tried many thing without success. I am trying to obtain the amount
Click to expand...
Click to expand...
over
5
but rounded to .00 A field in my report has it's control source to
[ExcMoisture]

The query field below is named [ExcMoisture]

IIf([Moisture]>5,Round([Moisture],2)-5,"0") I keep geting this result:
3.705, I have to have a result of: 3.71, other calculations will be based
on 3.71 not 3.705.

Thanks for any ideas...Randy
Click to expand...
Click to expand...
Click to expand...
 
Ahhh...time for me to correct my "cheat sheet"! Thanks.
--
Ken Snell
<MS ACCESS MVP>

John Spencer (MVP) said:
Ken,

Minor quibble. Int will handle Numbers of the type double. CInt will handle
numbers in the range plus/minus 32,767.

Just being picky this morning.

Ken said:
Yes, that is because the CLng will round down when the last digit is even
and the digit being dropped is a 5:
3.705 goes to 3.70
3.715 goes to 3.72

If you want it to go up, you can use either of these expressions:
IIf([Moisture]>5, (-Int(-[Moisture]*100)/100)-5, 0)
(this will work so long as Moisture will not exceed 32,767)

IIf([Moisture]>5, (CLng(([Moisture]+0.0001)*100)/100)-5, 0)

--
Ken Snell
<MS ACCESS MVP>

Randy said:
Ken, I tried this expression, but I am getting 3.7 rather than 3.71
thanks..Randy

IIf([Moisture]>5, (CLng([Moisture]*100)/100)-5, "0")

Any particular reason you're putting zero inside quotes to make it a
string?
Seems unnecessary.

IIf([Moisture]>5, (CLng([Moisture]*100)/100)-5, 0)
--
Ken Snell
<MS ACCESS MVP>

I have a report based on a query with various calculations. Below is
the
expression I am using, but it is not rounding to the hundreths. I have
tried many thing without success. I am trying to obtain the
Click to expand...
amount
over
5
but rounded to .00 A field in my report has it's control source to
[ExcMoisture]

The query field below is named [ExcMoisture]

IIf([Moisture]>5,Round([Moisture],2)-5,"0") I keep geting this result:
3.705, I have to have a result of: 3.71, other calculations will be
based
on 3.71 not 3.705.

Thanks for any ideas...Randy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top