NZ Returns #Error

[Guest]

I am using Access 2000.
I have a report that is fed by a sumquery. The report wouldn't total, I
assume, because I had fields in my sum formula that were blank in the
sumquery as they were carried over to the report. I noticed the NZ function
in this board and tried it. It seems to work in all my report fields except
where the text box name and the control are the same. When I use it in that
control source it returns an #Error. Does NZ not work in this instance? If
so how can I get around this issue without having to start renaming fields.

 

[Allen Browne]

If a control (e.g. text box) has the same name as a field in the
RecordSource of the form/report, Access gets confused if you then bind it to
an expression, such as one involving an Nz().

You must rename the text box to bind it to an expression.
And don't forget to include the = at the start.

 

[Guest]

Thanks Allen. That is what I was afraid of.

I have renamed the text box. Now when I preview the report it asks "Enter
Parameter Value" for the new text box name.

 

[Allen Browne]

In that case, the report contains a name that is not in its Record Source
table/query.

The title of the Parameter dialog will let you know what the name is.

If there is no control with that name, it may be in the Sorting and Grouping
dialog, or in one of the report's properties (such as Filter.)

If you are completely stuck, you might try adding the control with the right
name onto the report. Set is Visible property to No if you wish.

 

[Guest]

Thanks again Allen.
--
dmario

In that case, the report contains a name that is not in its Record Source
table/query.

The title of the Parameter dialog will let you know what the name is.

If there is no control with that name, it may be in the Sorting and Grouping
dialog, or in one of the report's properties (such as Filter.)

If you are completely stuck, you might try adding the control with the right
name onto the report. Set is Visible property to No if you wish.