Access 2010
I have a report and I want to place a text box that will evaluate a field called [Contract Owner]. The evaluation I want is "If the [Contract Owner] field for this record is empty, then place "N/A" in that record on the report, otherwise if the [Contract Owner] field is not empty then go ahead and place the value for that record.

I tried to create a formula using IIF() and IsNull() but could not get it to work. Seems simple, but I can't find an example on how to do this.
Thanks!

Hi,



Have you tried?

=IIf([Contract Owner] & "" = "", "N/A", [Contract Owner])

Hope that helps...

=IIf([Contract Owner] & "" = "", "N/A", [Contract Owner]) returns a #Type! error message on the report.

(In the design view, I get the message: "This control has a reference to itself" )

PMFJI:

This can happen when the control has the same name as the field to which it is bound.

Try renaming the control on the report to txtContractOwner and then use that name in your IIF() expression.

Hi
Another option would be to take that decision back to the underlying source of the report (Query) and do your condition at query level then bring that result out using a bound control.

My 2 euro's worth...

Perfect! renaming fixed it and it works perfectly now. Thanks so much to everyone.
One more thing, I would like to understand why this works, I have used If/Then statements quite frequently in Excel but I do not understand how this particular statement is working.

=IIf([Contract Owner] & "" = "", "N/A", [Contract Owner])

Is the ""="" somehow representing an empty field?

Thanks again,
Tom

Hi Tom,

Glad to hear you got it sorted out. The expression:

If [Something] & "" = "" Then

Evaluates True if [Something] is either Null or ZLS.

Hope that helps...

Thanks again for the explanation. I'm trying to learn as I go.

Hi,




Good luck with your project.

another solution would be to set the Format property of the [Contract Owner] textbox control in the report to

@;"N/A"

if there is a value in the field, it displays. if the field is null, or a zero-length string, the "N/A" displays. this works with form controls, too, btw.

hth
tina

Hi Tina,

Nice trick!