Limiting Decimal Places in Report

[De Soup]

I am hoping to find a top level fix to a report I've inherited.

16 reports pop up from a macro that I believe was built with Access 2000.
When you run the macro in Access 2007, the reports display the figures up to
14 decimal places. I have formatted the field in the report to "standard"
format and the decimal places to "2" which has not solved the problem.

The queries contains the following:

round( (((Count(TAB_Reg.Badge))/(@prcntot*1.0))*100),2) as [Percentage<BR>]


I've read the posts on here, and they all go back to the query level for the
fix. I can't begin count the number of queries that would need updating.

Why are the properties that are set not working? Can I get a solution so I
only have to fix the 16 reports rather than all the queries from where the
data comes?

 

[Allen Browne]

If Access takes no notice when you set the Decimal Places property of the
text box to 2, it would suggest that it is not understanding the value as a
number, but as a piece of text. The best solution would be to go back to the
query and change the field so Access understands the data type correctly.

If that is not possible, you might be able to force the report to interpret
it as you expect by using Val(), which returns the numeric value of a piece
of text. Val() can't cope with nulls, so you would also need to use Nz(). So
if the query field is named Field1, the ControlSource of the text box would
be:
=Val(Nz([Field1], "0"))

Be sure to change the Name of the text box too: Access gets confused if its
Name property is the same as a field, but it is bound to something else.

Depending on the Sorting'n'Grouping in the report, this may trigger another
error which occurs when you refer to a field in the report's RecordSource
that is not represented by a control on the report (and so the reports
optimizer doens't bother fetching the value.) To work around this, you would
need to leave the (hidden) text box on the report as well as the one that
has the suggested ControlSource.

That's why your research revealed that the real solution is to get the field
right in the query, rather than try to patch up the damage in the report.

 

[will.b]

Depending on your application, this may be a quick fix as well:

If you know that none of your resulting values (or the ones you intend to augment) will be negative, refer to it in your expression as Abs(Date1-Date2 or whatever), and now in the properties pane you can select "Fixed" - it will now default to your decimal place settings. It seems to override the original logic determining decimal placement. It will still be solely number based, and I know it's a little on the sloppy side but it did the trick for me. It may be doing the same thing as the Val() statement, but I was having toruble getting that to take in my instance.