Remove Null fields from reports?

[Asle]

Hi All,


I am using ms access 2007/windows Vista

I am trying to generate a report for a printing company based on the quote prepared for a client.

in the report, i want to include only the fields which are has an entry and exclude the rest.

i was wondering is it possible to create a report to acheive this.


as of now, i have all the fields in the report ,based on a parameter query.

need someone's assistance..


chuz,
Asle

[nico5038]

Not sure which fields you want to hide, but I did once create a function to dynamically fill fields on a report for a crosstable query.
Such a query can have a different number of fields and perhaps it's suitable for you to use.

Making the columnheader and detaildata flexible is possible, but needs some VBA code in the OpenReport event.

To start, doing this you need to place the fields "coded" in the report.
The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
The "detail" fields should be called "Col1", "Col2", "Col3", etc.

The report query has two rowheader columns and a Total column, therefor the first field is effectively column 4 (count starts at 0 so I used intI=3) but this could differ for you.

Make sure that the number of Columns is not bigger as the number placed. The programcode has no protection against that !

The OpenReport code:

Private Sub Report_Open(Cancel As Integer)
Dim intI As Integer
 
Dim rs As Recordset
 
Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
 
'Place headers
For intI = 3 To rs.Fields.Count - 1
Me("lblCol" & intI - 1).Caption = rs.Fields(intI).Name
Next intI
 
'Place correct controlsource
For intI = 3 To rs.Fields.Count - 1
Me("Col" & intI - 1).ControlSource = rs.Fields(intI).Name
Next intI
 
'Place Total field
Me.ColTotal.ControlSource = "=SUM([" & rs.Fields(2).Name & "])"
 
End Sub
 

The report query has two rowheader columns and a Total column, therefor the first field is effectively column 4 (count starts at 0 so I used intI=3) but it could differ for you.

Nic;o)