Access 2K - Error in "Detail" when bound TextBox in "Report Footer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report in Access 2K (running on Win98SE) which was working up until
a week or so ago. Then, it started giving me an #Error in the detail section
when a negative value was fed in, but only if there is a bound TextBox in the
Report Footer section. Lines in the Detail section which have positive
values work properly. Also, the summary values in the Report Footer section
are correct.

The query which feeds the report is outputting the correct value.

I have done all the usually things of: 1) create a new report, importing the
sections in from the old one; 2) create a new database, importing all the
things in from the old one; 3) create a new report, building the sections
from scratch. This last one works properly until I put a bound TextBox into
the Report Footer section. An unbound TextBox still allows the Detail
section to properly show negative values.

Both Win98SE and Office 2K are fully patched. I tried this database/report
in a Win2K/Office 2K environment on the same computer and got the same error.
 
Probably a naming conflict, or a data type issue.

Is this a calculated field in your query? If so, try typecasting the
calculation to help Access to understand it. Then in the report, set the
Format property of the text box in the detail section to General Number or
similar, and do the same with the text box in the group footer. More info:
http://allenbrowne.com/ser-45.html

If that is not the issue, what is the Name of the query field, the name of
the text box in the Detail section, and the Name of the text box in the
report footer section? There's a list of bad names in this utility:
http://allenbrowne.com/AppIssueChecker.html

What is the Control Source of the text box in the group footer?
 
We are not able to open everyone's databases.

What we try to do is give you suggestions to help you solve your own issues,
and hopefully learn about what caused them along the way so you know how to
avoid them next time.
 
Allen Browne said:
Probably a naming conflict, or a data type issue.

Is this a calculated field in your query? If so, try typecasting the
calculation to help Access to understand it. Then in the report, set the
Format property of the text box in the detail section to General Number or
similar, and do the same with the text box in the group footer. More info:
http://allenbrowne.com/ser-45.html
Click to expand...

This had no effect on the problem, either positively nor negatively.
If that is not the issue, what is the Name of the query field, the name of
the text box in the Detail section, and the Name of the text box in the
report footer section? There's a list of bad names in this utility:
http://allenbrowne.com/AppIssueChecker.html
Click to expand...

Your tool won't run against my database. Error message is:
Error 3734: The database has been placed in a state by user 'Admin'
on machine Athlon that prevents it from being opened or locked.

There is no security / passwords / etc. on that database that would prevent
you program from opening it. :-(
What is the Control Source of the text box in the group footer?
Click to expand...

The query that feeds this report has 3 fields:
pName - text
PositiveBalance - Currency
NegativeBalance - Currency

The Control Source in the Detail section is =Abs([NegativeBalance]) with a
Format of Currency.
The Control Source in the Report Footer is =Abs(Sum([NegativeBalance])) with
a Format of Currency.
The result in the Report Footer is correct.
However, the value in the Detail section gives #Error if there is a bound
control in the Report Footer.

BTW, the field that gets the PositiveBalance in the Detail section works
just fine.
 
What is the Name of the control in the Detail section that has Control
Source of:
=Abs([NegativeBalance])
Make sure the name is not NegativeBalance (or any other field name.) Access
gets confused if the control has the same name as a field, but is bound to
something else. Similarly, check the name of the text box in the Report
Footer.

If that does not fix the problem, try adding another text box to the Detail
section of the form with these properties:
Name NegativeBalance
Control Source NegativeBalance
Visible No
Sometimes this helps the footer control to find what you are trying to sum.

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

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

McSummation said:
Allen Browne said:
Probably a naming conflict, or a data type issue.

Is this a calculated field in your query? If so, try typecasting the
calculation to help Access to understand it. Then in the report, set the
Format property of the text box in the detail section to General Number
or
similar, and do the same with the text box in the group footer. More
info:
http://allenbrowne.com/ser-45.html
Click to expand...

This had no effect on the problem, either positively nor negatively.
If that is not the issue, what is the Name of the query field, the name
of
the text box in the Detail section, and the Name of the text box in the
report footer section? There's a list of bad names in this utility:
http://allenbrowne.com/AppIssueChecker.html
Click to expand...

Your tool won't run against my database. Error message is:
Error 3734: The database has been placed in a state by user 'Admin'
on machine Athlon that prevents it from being opened or locked.

There is no security / passwords / etc. on that database that would
prevent
you program from opening it. :-(
What is the Control Source of the text box in the group footer?
Click to expand...

The query that feeds this report has 3 fields:
pName - text
PositiveBalance - Currency
NegativeBalance - Currency

The Control Source in the Detail section is =Abs([NegativeBalance]) with a
Format of Currency.
The Control Source in the Report Footer is =Abs(Sum([NegativeBalance]))
with
a Format of Currency.
The result in the Report Footer is correct.
However, the value in the Detail section gives #Error if there is a bound
control in the Report Footer.

BTW, the field that gets the PositiveBalance in the Detail section works
just fine.
Click to expand...
 
Allen Browne said:
What is the Name of the control in the Detail section that has Control
Source of:
=Abs([NegativeBalance])
Make sure the name is not NegativeBalance (or any other field name.) Access
gets confused if the control has the same name as a field, but is bound to
something else. Similarly, check the name of the text box in the Report
Footer.
Click to expand...

The control in the Detail section is named cNegativeBalance and in the
Report Footer is named NegBal
If that does not fix the problem, try adding another text box to the Detail
section of the form with these properties:
Name NegativeBalance
Control Source NegativeBalance
Visible No
Sometimes this helps the footer control to find what you are trying to sum.
Click to expand...

I did this but left Visible as Yes. The value displayed is a square box.

Changing Visible to No had no affect on the error I'm getting.

Your note above kind of confused me. The Report Footer is getting the
proper values. It's the value in the Detail section that is getting the
error.

I should have done a "level set" in my first post. I've been writing Access
databases for 10 years, so this isn't some "simple noob problem". ;-)
 
Okay, we have our first clue here. The text box named NegativeBalance, with
a ControlSource of negative balance shows the result as a square box?

That indicates either a corruption of the data, or a misinterpretation of
the data type. The value shows up fine in other contexts, so we will assume
Access is not interpreting the value correctly. While this is common for a
calculated field, it is really uncommon for a control bound directly to a
table field.

Set the Format property of the text box to Currency or General Number or
something that instructs Access to understand the value as numeric (i.e. not
interpret the binary value as text.)

You already have Name AutoCorrect turned off, and you have compacted after
that, so it cannot be Access identifying the control source with the wrong
field. I cannot imagine what else could be named NegativeBalance in the
entire database that it could be getting confused with.

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

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

McSummation said:
Allen Browne said:
What is the Name of the control in the Detail section that has Control
Source of:
=Abs([NegativeBalance])
Make sure the name is not NegativeBalance (or any other field name.)
Access
gets confused if the control has the same name as a field, but is bound
to
something else. Similarly, check the name of the text box in the Report
Footer.
Click to expand...

The control in the Detail section is named cNegativeBalance and in the
Report Footer is named NegBal
If that does not fix the problem, try adding another text box to the
Detail
section of the form with these properties:
Name NegativeBalance
Control Source NegativeBalance
Visible No
Sometimes this helps the footer control to find what you are trying to
sum.
Click to expand...

I did this but left Visible as Yes. The value displayed is a square box.

Changing Visible to No had no affect on the error I'm getting.

Your note above kind of confused me. The Report Footer is getting the
proper values. It's the value in the Detail section that is getting the
error.

I should have done a "level set" in my first post. I've been writing
Access
databases for 10 years, so this isn't some "simple noob problem". ;-)
Click to expand...
 
Back
Top