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.

 

[De Soup]

Thank you for your help, Allen.

Being a beginner with Access, it seems to me the problem is that the query
is running a count and dividing that by the "@prcntot".

This is the SQL currently in the query:
Count(TAB_Reg.Badge) AS [Cummulative<BR>Total<BR>],
round( (((Count(TAB_Reg.Badge))/(@prcntot*1.0))*100),2) as [Percentage<BR>]

The numeric value is the Count, not the Field. Shouldn't Access understand
that as a Number? Also, I'm not sure how to add "=Val(Nz([Field1], "0"))" to
the existing SQL.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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]

When you view the query datasheet results, does the column left-align (like
text), or right-align (like numbers)?

If right, what I suggested is not the solution. If left, wrap the expression
in Val(Nz()), e.g.:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your help, Allen.

Being a beginner with Access, it seems to me the problem is that the query
is running a count and dividing that by the "@prcntot".

This is the SQL currently in the query:
Count(TAB_Reg.Badge) AS [Cummulative<BR>Total<BR>],
round( (((Count(TAB_Reg.Badge))/(@prcntot*1.0))*100),2) as
[Percentage<BR>]

The numeric value is the Count, not the Field. Shouldn't Access
understand
that as a Number? Also, I'm not sure how to add "=Val(Nz([Field1], "0"))"
to
the existing SQL.

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.

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?

 

[De Soup]

The results format to the left in the datasheet view, however, when I put in
the NZ, I get an ADO error: 'Nz" is not a recognized built-in function name.
I checked the Access article at
http://office.microsoft.com/en-us/access/HA012288901033.aspx. I dont' see
why it's not accepting. I tried to leave out the Nz and it disliked the Val
function next.

When you view the query datasheet results, does the column left-align (like
text), or right-align (like numbers)?

If right, what I suggested is not the solution. If left, wrap the expression
in Val(Nz()), e.g.:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your help, Allen.

Being a beginner with Access, it seems to me the problem is that the query
is running a count and dividing that by the "@prcntot".

This is the SQL currently in the query:
Count(TAB_Reg.Badge) AS [Cummulative<BR>Total<BR>],
round( (((Count(TAB_Reg.Badge))/(@prcntot*1.0))*100),2) as
[Percentage<BR>]

The numeric value is the Count, not the Field. Shouldn't Access
understand
that as a Number? Also, I'm not sure how to add "=Val(Nz([Field1], "0"))"
to
the existing SQL.

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.

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 you are doing this in Access, it sounds like you have a problem with your
library references.

Info about solving that:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The results format to the left in the datasheet view, however, when I put
in
the NZ, I get an ADO error: 'Nz" is not a recognized built-in function
name.
I checked the Access article at
http://office.microsoft.com/en-us/access/HA012288901033.aspx. I dont' see
why it's not accepting. I tried to leave out the Nz and it disliked the
Val
function next.

When you view the query datasheet results, does the column left-align
(like
text), or right-align (like numbers)?

If right, what I suggested is not the solution. If left, wrap the
expression
in Val(Nz()), e.g.:

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