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.
-
-
Could this be done by changing the 'Can Shrink' property of the appropriate field to 'Yes'? -
Hi Hedges98 - I tried that, however I did not see anything changing.
Thank you.Comment
-
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
endifComment
-
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.Comment
-
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/299011Comment
-
align columns with code
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.
Code: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_cswArrangeColumnsComment
-
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
Then repeat that for all remaining columns. Really would be better done as an array with a loop.Code: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 endifComment
-
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.Comment
-
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.
StanComment
-
Thanks Jim. It is working fine. Appreciate your help.
StanComment
Comment