Solved Conditional report items to display

[silversun]

Hello,
I have a report based on a query. Query is in order based on a table. I need one of the fields in my report shows "Not Applicable" when a criteria is met.
I used this code in expression builder but it didn't work:

= iff("Home","NOT APPLICABLE", [place_detail])

I was trying to say if [place_detail] = "Home" then change the word "Home" to "NOT APPLICABLE"
Is there anyway of doing it properly?
Thank you

 

[zeroaccess]

IIf([place_detail]="Home","Not Applicable",[place_detail])

 

[silversun]

Description: IIf([place_detail]="Home","Not Applicable",[place_detail])

Thanks for respond.
I applied your changes but it gave me errors next to the control in design view and when I ran it it goes into a circular prompt to enter "iff" in a dialog box.

Invalid Control Property
Circular Reference
I was thinking of using Conditional Formatting but it is used to color the control in different conditions which is not my case.

 

[zeroaccess]

Hmm not sure why that didn't work, but try this.

IIf([place_detail]="Home",Replace([place_detail],[place_detail],"Not Applicable"))

 

[silversun]

Hmm not sure why that didn't work, but try this.

IIf([place_detail]="Home",Replace([place_detail],[place_detail],"Not Applicable"))

I did. Exactly same errors displayed.

 

[theDBguy]

Thanks for respond.
I applied your changes but it gave me errors next to the control in design view and when I ran it it goes into a circular prompt to enter "iff" in a dialog box.

Invalid Control Property
Circular Reference
I was thinking of using Conditional Formatting but it is used to color the control in different conditions which is not my case.

Hi. Pardon me for jumping in; but first of all, make sure you spell it as "IIf" (two i's) and not "Iff" (two f's). And second, change the name of the Textbox to something else other than the name of the field.

 

[zeroaccess]

You are using this in the query design, right?

 

[silversun]

You are using this in the query design, right?

View attachment 81180

No. I was using it in expression builder. Let me try it in query design and get back to you.

 

[silversun]

You are using this in the query design, right?

View attachment 81180

I used your code in query design. Now it is showing all the values where changed to "NOT APPLICABLE". It is doing the "true" part of the statement properly but the "false" section remains blank, returns nothing.
Please see the snapshot:

 

[zeroaccess]

Ok, progress! That is because I didn't include a condition if False. A small modification:

IIf([place_detail]="Home",Replace([place_detail],[place_detail],"Not Applicable"),[place_detail])

Also are you sure this doesn't work? Try again in query design:

IIf([place_detail]="Home","Not Applicable",[place_detail])

 

[silversun]

Ok, progress! That is because I didn't include a condition if False. A small modification:

IIf([place_detail]="Home",Replace([place_detail],[place_detail],"Not Applicable"),[place_detail])

Also are you sure this doesn't work? Try again in query design:

IIf([place_detail]="Home","Not Applicable",[place_detail])

Now the query works fine. I am trying to use that field of the query (Expr2) as a source of the same field in my report but it gives me error.

Expr2: IIf([place_detail]="Home","DOESN'T APPLY",[place_detail])

My mistake was a typo at the beginning. I had iff instead of iif.
Sorry for my mistake.

 

[theDBguy]

My mistake was a typo at the beginning. I had iff instead of iif.
Sorry for my mistake.

I guess you missed my post about that.

 

[zeroaccess]

I use that as the control source for a text box on my report and for a field in datasheet view on a form. What I posted is actually a copy and paste from my DB, with changes to your specific names.

 

[silversun]

Thank you for your help.