Prev: Access 2010 - SQL view removed from queries!
Next: MS Access query
From: Gilgamesh on 16 Jun 2010 12:17
Hi,
I have a field in a report. I've specified that 3 digits be
displayed. The underlying column in the select that the report is
based on is of type double. How do I prevent the report rounding the
answer? If the value in the column is 3.0001 or 3.0009 I want the
report to display 3.000, but I cannot find a way to prevent the report
displaying 3.001 in the second case.
If anybody can tell me what to do, I'd be very grateful. Looking at
"Rounding" in Access help or in the Access books I have does not help.

Thanks,

Mike

From: James A. Fortune on 16 Jun 2010 12:45
On Jun 16, 12:17 pm, Gilgamesh <michaelsfl...(a)gmail.com> wrote:
> Hi,
> I have a field in a report.  I've specified that 3 digits be
> displayed.  The underlying column in the select that the report is
> based on is of type double.  How do I prevent the report rounding the
> answer?   If the value in the column is 3.0001 or 3.0009 I want the
> report to display 3.000, but I cannot find a way to prevent the report
> displaying 3.001 in the second case.
> If anybody can tell me what to do, I'd be very grateful.   Looking at
> "Rounding" in Access help or in the Access books I have does not help.
>
> Thanks,
>
> Mike

You want to "chop" rather than round. Try (air code):

=Int([MyField] * 1000) / 1000

as the control's ControlSource to get an output of 3 when MyField is
3.0001 or 3.0009. That expression should chop/lop/crop any decimal
places after the third decimal place. Then use the control's Format
property to force the display to three decimal places. Maybe also use
Nz([MyField], 0) instead of [MyField] in the expression if MyField is
allowed to have Null values so that you don't get something like
#Error showing up on your report.

James A. Fortune
CDMAPoster(a)FortuneJames.com
From: David W. Fenton on 16 Jun 2010 16:16
Gilgamesh <michaelsflynn(a)gmail.com> wrote in
news:965b8426-ff21-49f3-9e9a-1f10cffa2240(a)40g2000pry.googlegroups.com
:

> I have a field in a report. I've specified that 3 digits be
> displayed. The underlying column in the select that the report is
> based on is of type double. How do I prevent the report rounding
> the answer? If the value in the column is 3.0001 or 3.0009 I
> want the report to display 3.000, but I cannot find a way to
> prevent the report displaying 3.001 in the second case.
> If anybody can tell me what to do, I'd be very grateful. Looking
> at "Rounding" in Access help or in the Access books I have does
> not help.

You don't want to round, you want to truncate the value. It's a
little tricky to do that -- you'll need the non-integer part and
then use Left(3) on it. This won't work if you want the field to be
editable, though.

Access formats automatically round, and I don't think there's any
way around that, unfortunately.

It sounds to me that you're asking for the display to be inaccurate.
That is, if the stored value is 3.0009 and you display 3.000, that's
not accurate. Perhaps what you really want to do is truncate the
stored value, so that if the number is 3.0009, you want to store
3.000. That would take care of the format and rounding issues, but
it may be unsatisfactory.

But my view of it is that if it's unsatisfactory for storage, then
it's also unsatisfactory for display, as it's displaying what is, in
fact, in accurate data.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 | 
Pages: 1
Prev: Access 2010 - SQL view removed from queries!
Next: MS Access query