Cant round query result to nearest hundreth

[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

 

[Randy]

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 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 Snell]

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>

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 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 Snell]

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

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.

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>

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

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