NZ #Error on report

  • Thread starter Thread starter Amy Blankenship
  • Start date Start date
A

Amy Blankenship

I don't use Access reports very often, so please excuse the stupid question.
I have several controls that are (or should be) showing numeric values on my
form. However, if the field is null, they don't show anything. That's
fine, sez I, I'll just change the data bound to the control from MyField to
Nz(MyField, 0). The problem is, this gives an error. On a hunch, I changed
the name of one of these fields and dragged a second copy of the control
from the field list and made it invisible. The renamed control then showed
the correct value. However, I'd rather not have two sets of every control,
one with the value, and one that does the NZ on it. I have a feeling I've
missed something here, but I'm not sure what.

Thanks;

Amy
 
You probably do need the (hidden) text boxes bound to these fields to get
your report to work, Amy.

It seems that the report optimizer it is a bit too clever. It doesn't bother
retrieving all the fields in the report's RecordSource, unless it can see a
need to. It sees the need if there is a control bound directly to the field,
or the field is used in the Sorting'n'Grouping. But it doesn't check all the
expressions, so a valid expression may fail unless you actually have a
control for the field on the report.

A workaround for this case might be to leave the control bound directly to
the field (no nz()), and supply a 4-part expression in the control's Format
property, so it displays a zero for the null value.
 
Allen Browne said:
You probably do need the (hidden) text boxes bound to these fields to get
your report to work, Amy.

It seems that the report optimizer it is a bit too clever. It doesn't
bother retrieving all the fields in the report's RecordSource, unless it
can see a need to. It sees the need if there is a control bound directly
to the field, or the field is used in the Sorting'n'Grouping. But it
doesn't check all the expressions, so a valid expression may fail unless
you actually have a control for the field on the report.

A workaround for this case might be to leave the control bound directly to
the field (no nz()), and supply a 4-part expression in the control's
Format property, so it displays a zero for the null value.
Click to expand...

I'm not sure how to do that, but what I _did_ do is to change the name of
the control. When the control name is not the same as the field name, then
it seems that the control binds directly to the field. Could you provide an
example of the four-part expression?

Thanks;

Amy
 
Allen Browne said:
Try:
+0.0;–0.0;0.0;0

See the help file for details.
Click to expand...

I looked under "report format", and these were the topics that came up:

Troubleshoot controls
Troubleshoot replication
Format Event
Web: About ReportML
Troubleshoot charts in a form or report
Graphic filters and file formats Microsoft Access can use
OutputTo Action
Troubleshoot converting an Access file
Set the display format for data in a field or control
Export data or database objects
Import of link data and objects
Undo changes
Keyboard Shortcuts
Retreat Event
Print Event

I clicked on "Set the display format for data in a field or control",
because it was the only one that looked likely. When it opened, it had this
information:
Set the display format for data in a field or control
Set the data display format for a field in a table

1.. Open a table in Design view.
2.. In the upper portion of the window, click the field you want to
format.
3.. In the lower portion of the window, do one of the following:
a.. Click the arrow next to the Format property box, and then click one
of the predefined formats.
a.. Type a custom format in the Format property box for any field data
type other than OLE Object. Click the Format property box and press F1 for
more information about custom formats.
Set the data display format for a field in a query

1.. Open a query in Design view.
2.. In the query design grid, place the insertion point in the column for
the field you want to format. You can place the insertion point in any row
for that field.
3.. Click Properties on the Query Design toolbar to open the property
sheet for that field.
4.. On the General tab, do one of the following:
a.. Click the arrow next to the Format property box, and then click one
of the predefined formats.
a.. Type a custom format in the Format property box for any field data
type other than OLE Object. Click the Format property box and press F1 for
more information about custom formats.
Set the data display format for a control on a form, report, or data access
page



I didn't see anything there that explained what a four-part expression was,
or why you used the value you did. Could you be more specific about what
help topic I should look for?



Thanks;



Amy


..
 
If you're in Access 2007, the help topic is:
HV10082002

In previous versous, open the code window, and find Format Property. Then
choose:
Number and Currency Data Types
 
OK, thanks :-)

Allen Browne said:
If you're in Access 2007, the help topic is:
HV10082002

In previous versous, open the code window, and find Format Property. Then
choose:
Number and Currency Data Types
Click to expand...
 
FYI I worked around much the same problem using this formula instead:

=IIf(IsNull([field1]),[field2],[field1])

Still not sure why Nz doesn't work and this does ... hey ho.
 
Mike2 said:
FYI I worked around much the same problem using this formula instead:

=IIf(IsNull([field1]),[field2],[field1])

Still not sure why Nz doesn't work and this does ... hey ho.
Click to expand...

Don't call me that. :-)

But thanks :-p
 
Back
Top