Conditional formatting in Control Source 1

[cjc10]

Hi -

I have a report where the field ([cashieravg]) that I would like to format as $ or % is based on the value of another field ([format]). Both of these fields are on the report.

In testing this, I have tried the following in the Control Source :

=iif([format]="%",format([cashieravg],"0.00")&"%",1)

When I view the report, the value for [cashieravg] with % in the [format] field is #Error. The values in the [cashieravg] with something other than % in [format] shows correctly as 1.

I also tried just
=Iif([format]="%",[cashieravg],1)
and still get #Error.

Any ideas why it would show as #Error? Please let me know if something is unclear. Thank you so much in advance!

 

[dhookom]

Make sure the name of the control is not the name of a field in your report's record source. You may have an issue with using a function name as a field name ie: "Format". Format is a property and a function name and probably should have been avoided when naming fields. I would alias the field/column in the reports record source with an expression like:
Frmt:[Format]
and then try an expression in your control source like:

=IIf([frmt]="%",Format([cashieravg] * 100,"Percent"),"1")



Duane
Hook'D on Access
MS Access MVP

 

[cjc10]

Ah-ha! The name of the control was the same as a field name in the record source. Thank you so much for the tips!