Invisible field based on if statement

[rjmccafferty]

I have a series of dCount fields in the footer of a report. Some of them only apply to certain customers and I would prefer that unaffected customers not see the field to avoid confusion and needless questions.

Is there any way I can, in pseudo code, essentially say:
iif([field1]<>&quot;&quot;, =dCount(&quot;[field2]&quot;,&quot;Query1&quot;,&quot;[Fields2] = 'S' &quot,&quot;boundField.visible = No&quot

Or, in English,
if field1 is not empty, count field2 in Query1 where field2 = S,
else make the report field invisible

I just don't see anywhere in properties I can attach code to do this.

 

[Databaseguy]

Try the on format event of the report. Joe Miller taught me that one. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[rjmccafferty]

I could not find an &quot;On Format&quot; event in properties for the report. But I did find (duh!) Conditional formatting as an option under format in the main menu.

In reading my books, to augment the Help system for Access, I am not sure this will work in my instance. I am trying to format fields with DCount formulas in the fields. The books say that the ways it will work is if
1. the criteria are the value in the field (won't work because the condition refers to a field value in a different table),
2. When the field gets focus (I don't see how to make that work), or
3. when the expression evaluates to true. In which case the expression can refer to the values in other fields in the SAME record.

I am trying to look at using a query as the source of data where I can incorporate the field that determines the decision to be made in a way it is in the same record within the query.

It sounds like I could use an expression in the query where field1 (the subject of the IF that determines whether the bound field with the dCount will or will not be visible. Something like DecisionField: Iif(field2=&quot;Whatever&quot;,True, false&quot; allowing me to test at that point. But I haven't tries to accomplish this yet.

And if it does work, it does not appear it will work for the label field, still leaving me subject to a lot of questioning from report recipients for whom this feature does not apply. Does it sound like it should work or is there some gaping hole in my logic? Or something simple that I am missing?

 

[tempclerk]

On Format is a property of each Section of your report. Put your code in the section with the controls you are trying to hide. You can make the labels invisible this way too.

 

[rjmccafferty]

I see it. Thank you very much.