isnull [Label].visible=false not working-- need loop? -- 2nd Post 1

[factotum]

Hi All. I tried posting this to the Access Reports Forum but didn't get a response. Anyone here have a good idea how to accomplish this? Thanks.

I have a report that has the labels in a group heading but the containers in the details section. This way, I'm not repeating the Labels with every record.

The records returned in can vary from null to Many. I have the Labels set up to not be visible when null using:

 If not isnull([Field]) then
      me![FieldLabel].Visible = true
 Else
      me![FieldLabel].Visible = false
 Endif

However, if the first returned record is null, but the second one isn't, the labels disappear. Not really the effect I was after.

Any one know how to cycle through the returned records to ensure that they are all null before setting the visibility to false. Thanks.

=D

 

[nabiS2K]

How about you change your label to a text box and give it a source code of:

=IIF([Field]=Null,&quot; &quot;,&quot;<Enter Your Caption Here>&quot;)

Let me know if this doesn't work for you. I can help with the cycling through all records, but you might like this better.

God Bless

 

[jioek]

try adding that statement in the onformat section of the report property

 

[factotum]

I appreciate the advice, but the problem with that solution is that the box won't collapse (right?). I was hoping to save a few trees Won't your solution still have that space there for an empty text box?

What I'm trying to use now is:

  If [Field_Count] > 0 Then
      Me![FieldLabel].Visible = True
 Else
      Me![FieldLabel].Visible = False
 End If

where I've set up a count([Field]) and called it "Field_Count." I would like to know how to loop, though, because this count function doesn't allow me to count some other data (where it IS null) and my own insatiable curiosity.

Thanks for your help.
=D

 

[factotum]

jjoek,

Thanks for the response. I'm not too familiar with the VB environment. But when I look at the code, it says "[Event Procedure] in my OnFormat box. So, I've already done what you say, correct?

Thanks,

=D

 

[RickSpr]

It sounds to me as if you want the label to be invisible only if the field is Null in all rows of your group, right?

As jioek indicated, you want to put the code in the group header's Format event procedure. However, the code you need has to test whether there are any non-null values in that group. I think you could use DLookup() to do the test. Say you're grouping by [Dept] and want to test whether the department has any non-Null values in [Project]. Then this should work:
If DLookup("Count([Project])", "[table or query name]", "[Dept] = '" & Me![Dept] & "'&quot > 0 Then ...

Note that the Count() SQL function, when used with a column name, doesn't count rows where the column is Null. That's just what you need! Also note that I've specified a where condition argument, because you only want to test the values for the current group. The where condition tests rows where the grouping field has the same value as the current record in the report, which is the one that started this group. (If you're grouping on a numeric field, you can omit the apostrophes.)
Rick Sprague