Hide fields in a report which do not have data

[mulamootil]

Is there a way to hide fields in a report that do not have any data. I have few fields that have images and I would like to hide them as well whenever there is none. I am using Access 07.

[hedges98]

Could this be done by changing the 'Can Shrink' property of the appropriate field to 'Yes'?

[mulamootil]

Hi Hedges98 - I tried that, however I did not see anything changing.

Thank you.

[jimatqsi]

In the format event, if the field is empty set the field's visible property to false
Assuming this is in the report detail, this code would be in the Detail section's format event

if isnull(me!field name) then
me!fieldname.vi sible = false
else
me!fieldname.vi sible = true
endif

[mulamootil]

Thank you so much. That was a great help. It worked just perfect. Formatting an Access report is really a pain. It still leaves huge gaps in my report and sometimes overlaps fields, but it definitely hides all the fields no data. Thanks again.

[hedges98]

To prevent the gaps, you could play around with the Can Shrink property I mentioned. Take a look at this for more information: http://support.microsoft.com/kb/299011

[jimatqsi]

Unfortunately, as that article mentions, can shrink only eliminates vertical white space, not horizontal white space. So in this case, if mulamootil made a column invisible, unsightly gaps across the line will appear.

Access report setup can be a pain. A little extra work goes a long way, though, to making a good result. I wrote this routine that I use to align columns perfectly next to each other. Mulamootil, you could alter this a little bit by adding a test for each column; if it is visible you include it in the placement of columns, if it is not visible, you can not add its width to the progression across the page.

There's additional room for improvement here. Probably an array of object names should be passed instead of a list of individual objects (text boxes and labels). Note that the first column object is not placed by this code, it is assumed to be the starting point. Here's the code, which belongs in the .format event.

Public Function cswArrangeColumns(Optional C1, Optional C2, Optional C3, Optional C4, Optional C5, Optional C6, Optional C7, Optional C8, Optional C9, Optional C10, Optional C11, Optional C12, Optional C13, Optional C14, Optional C15, Optional C16, Optional C17, Optional C18, Optional C19, Optional C20, Optional C21, Optional C22, Optional C23, Optional C24, Optional C25, Optional C26, Optional C27, Optional C28, Optional C29, Optional C30) As Double
Dim dblStart As Double
Dim dblLength As Double
Dim ctlControl As Control
Dim dblL As Double
Dim dblMinWidth As Double
Dim dblWidth As Double
 
dblMinWidth = 0.025
 
   On Error GoTo cswArrangeColumns_Error
'        dblWidth = C1.Width
'        If dblWidth = 0 Then dblWidth = dblMinWidth
        dblL = C1.Left + C1.Width
        C2.Left = C1.Left + C1.Width
        dblL = C2.Width + dblL
        C3.Left = C2.Left + C2.Width
        dblL = C3.Width + dblL
        C4.Left = C3.Left + C3.Width
        dblL = C4.Width + dblL
        C5.Left = C4.Left + C4.Width
        dblL = C5.Width + dblL
        C6.Left = C5.Left + C5.Width
        dblL = C6.Width + dblL
        C7.Left = C6.Left + C6.Width
        dblL = C7.Width + dblL
        C8.Left = C7.Left + C7.Width
        dblL = C8.Width + dblL
        C9.Left = C8.Left + C8.Width
        dblL = C9.Width + dblL
        C10.Left = C9.Left + C9.Width
        dblL = C10.Width + dblL
        C11.Left = C10.Left + C10.Width
        dblL = C11.Width + dblL
        C12.Left = C11.Left + C11.Width
        Dim strName As String
     '   strName = C12.Name
        dblL = C12.Width + dblL
        C13.Left = C12.Left + C12.Width
        dblL = C13.Width + dblL
        C14.Left = C13.Left + C13.Width
        dblL = C14.Width + dblL
        C15.Left = C14.Left + C14.Width
        dblL = C15.Width + dblL
        C16.Left = C15.Left + C15.Width
        dblL = C16.Width + dblL
        C17.Left = C16.Left + C16.Width
        dblL = C17.Width + dblL
        C18.Left = C17.Left + C17.Width
        dblL = C18.Width + dblL
        C19.Left = C18.Left + C18.Width
        dblL = C19.Width + dblL
        C20.Left = C19.Left + C19.Width
        dblL = C20.Width + dblL
        C21.Left = C20.Left + C20.Width
        dblL = C21.Width + dblL
        C22.Left = C21.Left + C21.Width
        dblL = C22.Width + dblL
        C23.Left = C22.Left + C22.Width
        dblL = C23.Width + dblL
        C24.Left = C23.Left + C23.Width
        dblL = C24.Width + dblL
        C25.Left = C24.Left + C24.Width
        dblL = C25.Width + dblL
        C26.Left = C25.Left + C25.Width
        dblL = C26.Width + dblL
        C27.Left = C26.Left + C26.Width
        dblL = C27.Width + dblL
        C28.Left = C27.Left + C27.Width
        dblL = C28.Width + dblL
        C29.Left = C28.Left + C28.Width
        dblL = C29.Width + dblL
        C30.Left = C29.Left + C29.Width
        dblL = C30.Width + dblL
Exit_cswArrangeColumns:
    cswArrangeColumns = dblL
    On Error GoTo 0
   Exit Function
 
cswArrangeColumns_Error:
    GoTo Exit_cswArrangeColumns
 

[jimatqsi]

To eliminate the gaps from the invisibles you could either avoid including the invisibles in the call to the function, or more elegantly, you could make the function smart enough to avoid including them in the movement across the page. I'd change the function like this

dim intWidth as integer
       c2.left = c1.left       ' added in case c2 is invisible
       intWidth = c1.width
       if c2.visible then    ' add this to ea object to elim gaps for invisibles
                                 ' only change col2's position if it is visible
         C2.Left = C1.Left + intWidth ' changed to intWidth from C1.width
         intWidth = c2.width      ' add this to ea obj 
         dblL = C2.Width + dblL  ' only increase add to the line's length if it is visible
       endif 
 

Then repeat that for all remaining columns. Really would be better done as an array with a loop.

[jimatqsi]

One more note to state the obvious, you would call this to format both the headings and the detail or totals that should appear in those columns.

[mulamootil]

Thanks Jimatqsi and Hedges98. I haven't gotten a chance to try this yet, however I sure see the light at the end of the tunnel. I will let you know the outcome. Again, I appreciate the input and thank you for the code Jim.

Stan