When Generating Report don't show columns which are empty

[billa856]

Hi,
>I have project in MS access.
>In that project I am generating some reports for my company.
>Now In my form I have one combobox(PalletNo_combo)
>when I select an item from combobox and then click on search button then it will generate Report on search criteria.
>Now in that report I have 15-20 fields like
PalletNo,OrderDate,CustmerCode,ShippingLocation,Ca rtons,PartialCartons,ShippedDate,TotalQuantity,Shi ppedQuantity etc
>Now when Report gnerated I have column PartialCarton which is empty sometimes bcz of Null value inside it.
>So I don't want to show it in report when whole column in empty or value is Null.
>If anyone have solution plz help me.
Thanks

[MMcCarthy]

Hi,
>I have project in MS access.
>In that project I am generating some reports for my company.
>Now In my form I have one combobox(PalletNo_combo)
>when I select an item from combobox and then click on search button then it will generate Report on search criteria.
>Now in that report I have 15-20 fields like
PalletNo,OrderDate,CustmerCode,ShippingLocation,Ca rtons,PartialCartons,ShippedDate,TotalQuantity,Shi ppedQuantity etc
>Now when Report gnerated I have column PartialCarton which is empty sometimes bcz of Null value inside it.
>So I don't want to show it in report when whole column in empty or value is Null.
>If anyone have solution plz help me.
Thanks

The simple answer is you can't.

If a column is defined on a report you can't hide it if the value is null/empty for all values.

[Minion]

What mmccarthy said is right from what I can tell, however there is a way to fudge it a little. WARNING: The following is not pretty and expects you know a little about coding and recordsets. Also depending on how many records there are in the report and the computing environment it may take a very long time to open.

Now as I was saying you can fudge it a little by putting a counter code into the open event of the report. This will need to open a recordset that is the same source of the report be it a table or a query. Then the particular record within the record set will need to be counted (generally this will be the column number counting from 0). Once counted we can do a simple "IF" to see if the counter is empty and then hide the label for the column on the report. This will leave a blank column unless you put it at the end of the report or you take the pains to code in a formatting block in the IF statement that will rearrange the report.

I'm sure there will be questions so I'll try and help as much as possible and am sure the experts here will be of more help with some of the more difficult parts.

Dim rst as RecordSet
Dim i as Integer
 
 
set rst = <<code to open recordset here>>
i = 0
 
   While Not rst.EOF
      If Not (isNull(rst.fields(<<field number.. again column number counting from 0>>))) Then
         i = i + 1
      End If
   Wend
 
   If i = 0 Then
      Me.Label.Visible = False
   End If
 

I hope this helps or at least gets you moving in the right direction. Again I will attempt to clear things up as questions arrise.

- Minion -

[MMcCarthy]

What mmccarthy said is right from what I can tell, however there is a way to fudge it a little.
>
>
I hope this helps or at least gets you moving in the right direction. Again I will attempt to clear things up as questions arrise.

- Minion -

Nice solution Minion.

The only other thing I can think of suggesting is to use Dynamic Reporting of some kind.

There's an article on it here.

Create Dynamic Report using VBA