How to make a calculated field always round up to 1st decimal place?!

J

James

Bit of an odd request probably, but I am sorting out some inconsistensies
between an access report and a customers' software package. Some of the
pages of the report are differing slightly from my report and I think the
problem is rounding. The software stores a certain item as a 1 decimal place
item, i.e. "0.2" or "1.3" and adds these.
To calculate these, you need to divide a database item by 3600, and for the
most part it works, however some of these come out with a recurring figure,
for instance 0.21694444444 or 0.21666666667. I am displaying them on my
report using the format command, but those two would always round to 0.2. I
think the discrepancy arises because the software *always* rounds up (i.e.
those examples should show as 0.3). Any way I can achieve this?
Thanks.
James
 
J

James

James said:
Bit of an odd request probably, but I am sorting out some inconsistensies
between an access report and a customers' software package. Some of the
pages of the report are differing slightly from my report and I think the
problem is rounding. The software stores a certain item as a 1 decimal place
item, i.e. "0.2" or "1.3" and adds these.
To calculate these, you need to divide a database item by 3600, and for the
most part it works, however some of these come out with a recurring figure,
for instance 0.21694444444 or 0.21666666667. I am displaying them on my
report using the format command, but those two would always round to 0.2. I
think the discrepancy arises because the software *always* rounds up (i.e.
those examples should show as 0.3). Any way I can achieve this?
Click to expand...


Answering my own question here, but figured out a way that seems to work
using the following expression in my report:

=Format(IIf([ElapsedTime]/3600<0.2,0.2,IIf(Len(CStr([ElapsedTime]/3600))>3,[
ElapsedTime]/3600+0.05,[ElapsedTime]/3600)),"0.0")

May be of use to others!
 
J

John Spencer (MVP)

Another method is to use

-Int(-Value*10)/10

That rounds any positive Value up to the nearest tenth. Change the ten to one
hundred to round up to the next hundredth.
James said:
Bit of an odd request probably, but I am sorting out some inconsistensies
between an access report and a customers' software package. Some of the
pages of the report are differing slightly from my report and I think the
problem is rounding. The software stores a certain item as a 1 decimal place
item, i.e. "0.2" or "1.3" and adds these.
To calculate these, you need to divide a database item by 3600, and for the
most part it works, however some of these come out with a recurring figure,
for instance 0.21694444444 or 0.21666666667. I am displaying them on my
report using the format command, but those two would always round to 0.2. I
think the discrepancy arises because the software *always* rounds up (i.e.
those examples should show as 0.3). Any way I can achieve this?
Click to expand...

Answering my own question here, but figured out a way that seems to work
using the following expression in my report:

=Format(IIf([ElapsedTime]/3600<0.2,0.2,IIf(Len(CStr([ElapsedTime]/3600))>3,[
ElapsedTime]/3600+0.05,[ElapsedTime]/3600)),"0.0")

May be of use to others!
Click to expand...
 
J

James

John Spencer (MVP) said:
Another method is to use

-Int(-Value*10)/10

That rounds any positive Value up to the nearest tenth. Change the ten to one
hundred to round up to the next hundredth.
Click to expand...
One to bear in mind, thanks!
 
Top