Calculated "Control"

  • Thread starter Thread starter Basharat A. Javaid
  • Start date Start date
B

Basharat A. Javaid

One of the fields in my table has zero value for first few records. For
better appearance, in my report I want to show blank if its value is zero.
I tried the following expression in the Control Source:

=IIF([field_x]>0,[field_x],""]

But I get #Error for all values of field_x!?
 
Basharat,

"" designates a text value, which cannot be placed in a number field.

Possibly the easiest approach would be to use the Format property of the
control in the design of the report. For what you want, it will
possibly be something like...
0;\-0; ;
 
Steve, I thought about that and tried the

=IIF([field_x]>0,[field_x],n], with various values of n=0, 1..,

but I still got the #Error message?

Basharat.

Steve Schapel said:
Basharat,

"" designates a text value, which cannot be placed in a number field.

Possibly the easiest approach would be to use the Format property of the
control in the design of the report. For what you want, it will
possibly be something like...
0;\-0; ;

--
Steve Schapel, Microsoft Access MVP

One of the fields in my table has zero value for first few records. For
better appearance, in my report I want to show blank if its value is zero.
I tried the following expression in the Control Source:

=IIF([field_x]>0,[field_x],""]

But I get #Error for all values of field_x!?
Click to expand...
Click to expand...
 
Basharat,

Hmmm. By some small quirk of fate, I don't suppose the control where
you are entering this control source expression is named field_x?
 
Yep - that was it. I changed the name to txtField_x and it works, even with
"" in the expression.
I forgot the Access 101 rule of prefixing control names with lbl, txt etc.

Thanks Steve.

Steve Schapel said:
Basharat,

Hmmm. By some small quirk of fate, I don't suppose the control where
you are entering this control source expression is named field_x?

--
Steve Schapel, Microsoft Access MVP

Steve, I thought about that and tried the

=IIF([field_x]>0,[field_x],n], with various values of n=0, 1..,

but I still got the #Error message?

Basharat.
Click to expand...
Click to expand...
 
Basharat,

Yes, I have heard of this "rule". I personally do not subscribe to this
idea. However, this was not exactly the problem in this case. Here's
the rule you were breaking... "Don't have a control with the same name
as a field in the record source if the control is not bound to that
field." Anyway, pleased to hear that it's working for you now.
 
Back
Top