Hiding Columns in a Report MELP ME

[tycanis]

I have been trying to figure this out for hours so let me run you through what I have.

I have a table with 36 fields. Three of these fields will always have data but all of the others will vary on whether they will contain or not. For some rows they might all contain info but for others only 50% will. The table is this way because it contains scan info broken down by time. This means for every employee number there is a total number of scans and date but then the remaining fields will break down the total scans based time period. The reason for the opportunity of empty fields is to allow comparison between employee's rate of scans and when one employee is scanning another might not be.

Alright so that is the table. I want to create a report of this table that will show employee ID number, total scans, date, and then any time periods where scans occurred. The issue is that I do not want blank columns to show up and I cannot get rid of them. I have tried the following in the Detail, On Format event along with other variations that are similar.

Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
If IsNull(ctl.Value) Then
ctl.Visible = False
End If
End If
Next ctl

This does not work and I cannot figure out why. I know that the empty columns are null and not "" and I have tried that variation anyways to see if it worked.

If anyone has ideas on how to accomplish what I am doing let me know. I am working in all versions from 2003 to 2010.

 

[SydneyGeek]

In the Detail_Format event, try using:

Dim ctl As Control
For Each ctl In Me.Detail.Controls
  If TypeOf ctl Is TextBox Then
    If IsNull(ctl.Value) Then
      ctl.Visible = False
    Else
      ctl.Visible = True
    End If
  End If
Next ctl

Changes: Used Me.Detail.Controls instead of Me.Controls, and added an Else statement to toggle the visibility of controls that have a value.
Denis