#name error

[Maverick]

I am baffled. My report has some unbound textboxes that perform calculations
based on other fields in the report ( e.g.
=[CountOfUniqueStates]+[SumOfCountNoState]+[SumOfCountNonUS] ). When the
report loads I get the #name error. All three of these fields are on the
report so it's not a question of control source. However, if I add a blank
textbox onto the report and view it, the #name error magically disappears.
Unfortunately, if I close the database and open the report, the #name error
returns. This is reproducable.

Oh, to make things more complicated, the page numbering in the footer has
the same problem. This is a built in function and it is producing the #name
error. It just doesn't make any sense.

Thanks in advance for any help you can provide.

 

[Allen Browne]

The problem you describe is a real one, and the solution (as you found) is
to place a text box on the report, bound to the field. Set the Visible
property of the text box to No if you wish, but it must be there.

I believe the problem is a case of Access trying to be too smart. When it
opens a report, if there is anything in the Sorting And Grouping box, it
doesn't just fetch all the data and then aggregate it. It actually creates a
super-level query (i.e. another query into the RowSource), and fetches that.
For performance reasons, this upper-level query doesn't get fields it
doesn't need.

That's all well and good, but if there is no control bound to a field (and
the field doesn't appear in the Sorting'n'Grouping), it may decide not to
fetch the field -- even if it *is* needed for part of another Control
Source. Hence, an expression (like the one you have) doesn't work, unless
you explicitly place controls on the report for those fields.

Futher, once a calculated control fails, Access gives up on trying to
calculate the others. The ones it did first work, but any it had not done
when the error occurred just show an error. That means that one uncalculable
control can cause other valid ones (like you page number control) to error
out too.

 

[Maverick]

Allen,

I follow the logic, but don't follow the solution. What I did was just a
temporary fix. I simply placed a control, any control, on the report and the
calculation worked fine. However, if I exit Access and then reopen the
report, the #name error returns.

You state that I need to place a textbox on the report bound to the field.
However, I'm not sure how I would bind a textbox to an unbound textbox which
is merely a calculation.

Thank you for your prior response. I hope that you can give a more detailed
explanation of the solution.

The problem you describe is a real one, and the solution (as you found) is
to place a text box on the report, bound to the field. Set the Visible
property of the text box to No if you wish, but it must be there.

I believe the problem is a case of Access trying to be too smart. When it
opens a report, if there is anything in the Sorting And Grouping box, it
doesn't just fetch all the data and then aggregate it. It actually creates a
super-level query (i.e. another query into the RowSource), and fetches that.
For performance reasons, this upper-level query doesn't get fields it
doesn't need.

That's all well and good, but if there is no control bound to a field (and
the field doesn't appear in the Sorting'n'Grouping), it may decide not to
fetch the field -- even if it *is* needed for part of another Control
Source. Hence, an expression (like the one you have) doesn't work, unless
you explicitly place controls on the report for those fields.

Futher, once a calculated control fails, Access gives up on trying to
calculate the others. The ones it did first work, but any it had not done
when the error occurred just show an error. That means that one uncalculable
control can cause other valid ones (like you page number control) to error
out too.

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

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

I am baffled. My report has some unbound textboxes that perform
calculations
based on other fields in the report ( e.g.
=[CountOfUniqueStates]+[SumOfCountNoState]+[SumOfCountNonUS] ). When the
report loads I get the #name error. All three of these fields are on the
report so it's not a question of control source. However, if I add a blank
textbox onto the report and view it, the #name error magically disappears.
Unfortunately, if I close the database and open the report, the #name
error
returns. This is reproducable.

Oh, to make things more complicated, the page numbering in the footer has
the same problem. This is a built in function and it is producing the
#name
error. It just doesn't make any sense.

Thanks in advance for any help you can provide.

 

[Allen Browne]

You said you have a text box with Control Source of:
=[CountOfUniqueStates]+[SumOfCountNoState]+[SumOfCountNonUS]

I assume:
- you have a query feeding the report,
- the query has the 3 fields named above,
- you don't have text boxes on the report for those 3 fields.

In the same section where the text box described above, add 3 text boxes.
The first will have a Name and ControlSource of:
CountOfUniqueStates
and so on for the other 2.
Save the report.

Now when you open the report, it should understand what you are referring to
in the expression above.

If that does not solve the problem, temporarily delete any other controls
bound to expresssions (such as your page number one), to eliminate the
chance that one of the others is the problem.

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

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

Allen,

I follow the logic, but don't follow the solution. What I did was just a
temporary fix. I simply placed a control, any control, on the report and
the
calculation worked fine. However, if I exit Access and then reopen the
report, the #name error returns.

You state that I need to place a textbox on the report bound to the field.
However, I'm not sure how I would bind a textbox to an unbound textbox
which
is merely a calculation.

Thank you for your prior response. I hope that you can give a more
detailed
explanation of the solution.

The problem you describe is a real one, and the solution (as you found)
is
to place a text box on the report, bound to the field. Set the Visible
property of the text box to No if you wish, but it must be there.

I believe the problem is a case of Access trying to be too smart. When it
opens a report, if there is anything in the Sorting And Grouping box, it
doesn't just fetch all the data and then aggregate it. It actually
creates a
super-level query (i.e. another query into the RowSource), and fetches
that.
For performance reasons, this upper-level query doesn't get fields it
doesn't need.

That's all well and good, but if there is no control bound to a field
(and
the field doesn't appear in the Sorting'n'Grouping), it may decide not to
fetch the field -- even if it *is* needed for part of another Control
Source. Hence, an expression (like the one you have) doesn't work, unless
you explicitly place controls on the report for those fields.

Futher, once a calculated control fails, Access gives up on trying to
calculate the others. The ones it did first work, but any it had not done
when the error occurred just show an error. That means that one
uncalculable
control can cause other valid ones (like you page number control) to
error
out too.

I am baffled. My report has some unbound textboxes that perform
calculations
based on other fields in the report ( e.g.
=[CountOfUniqueStates]+[SumOfCountNoState]+[SumOfCountNonUS] ). When
the
report loads I get the #name error. All three of these fields are on
the
report so it's not a question of control source. However, if I add a
blank
textbox onto the report and view it, the #name error magically
disappears.
Unfortunately, if I close the database and open the report, the #name
error
returns. This is reproducable.

Oh, to make things more complicated, the page numbering in the footer
has
the same problem. This is a built in function and it is producing the
#name
error. It just doesn't make any sense.

 

[Maverick]

Allen,

I have the 3 bound controls on the report, so that is not the issue.
However, I believe that the folks from X-files visited me as the issue has
resolved itself without anything I knowingly did. Before I submitted on this
forum I had tested my database on another computer and was able to reproduce
the error over and over again. Now it is magically resolved. I'm hoping it
doesn't rear its ugly head after implementation. For now it is resolved.

Thanks for your input.

You said you have a text box with Control Source of:
=[CountOfUniqueStates]+[SumOfCountNoState]+[SumOfCountNonUS]

I assume:
- you have a query feeding the report,
- the query has the 3 fields named above,
- you don't have text boxes on the report for those 3 fields.

In the same section where the text box described above, add 3 text boxes.
The first will have a Name and ControlSource of:
CountOfUniqueStates
and so on for the other 2.
Save the report.

Now when you open the report, it should understand what you are referring to
in the expression above.

If that does not solve the problem, temporarily delete any other controls
bound to expresssions (such as your page number one), to eliminate the
chance that one of the others is the problem.

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

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

Allen,

I follow the logic, but don't follow the solution. What I did was just a
temporary fix. I simply placed a control, any control, on the report and
the
calculation worked fine. However, if I exit Access and then reopen the
report, the #name error returns.

You state that I need to place a textbox on the report bound to the field.
However, I'm not sure how I would bind a textbox to an unbound textbox
which
is merely a calculation.

Thank you for your prior response. I hope that you can give a more
detailed
explanation of the solution.

The problem you describe is a real one, and the solution (as you found)
is
to place a text box on the report, bound to the field. Set the Visible
property of the text box to No if you wish, but it must be there.

I believe the problem is a case of Access trying to be too smart. When it
opens a report, if there is anything in the Sorting And Grouping box, it
doesn't just fetch all the data and then aggregate it. It actually
creates a
super-level query (i.e. another query into the RowSource), and fetches
that.
For performance reasons, this upper-level query doesn't get fields it
doesn't need.

That's all well and good, but if there is no control bound to a field
(and
the field doesn't appear in the Sorting'n'Grouping), it may decide not to
fetch the field -- even if it *is* needed for part of another Control
Source. Hence, an expression (like the one you have) doesn't work, unless
you explicitly place controls on the report for those fields.

Futher, once a calculated control fails, Access gives up on trying to
calculate the others. The ones it did first work, but any it had not done
when the error occurred just show an error. That means that one
uncalculable
control can cause other valid ones (like you page number control) to
error
out too.

I am baffled. My report has some unbound textboxes that perform
calculations
based on other fields in the report ( e.g.
=[CountOfUniqueStates]+[SumOfCountNoState]+[SumOfCountNonUS] ). When
the
report loads I get the #name error. All three of these fields are on
the
report so it's not a question of control source. However, if I add a
blank
textbox onto the report and view it, the #name error magically
disappears.
Unfortunately, if I close the database and open the report, the #name
error
returns. This is reproducable.

Oh, to make things more complicated, the page numbering in the footer
has
the same problem. This is a built in function and it is producing the
#name
error. It just doesn't make any sense.