By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Hide fields in a report which do not have data

P: 33
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.
Feb 26 '10 #1
Share this Question
Share on Google+
10 Replies


100+
P: 109
Could this be done by changing the 'Can Shrink' property of the appropriate field to 'Yes'?
Feb 26 '10 #2

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

Thank you.
Feb 26 '10 #3

Expert 100+
P: 1,221
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!fieldname) then
me!fieldname.visible = false
else
me!fieldname.visible = true
endif
Feb 26 '10 #4

P: 33
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.
Mar 2 '10 #5

100+
P: 109
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
Mar 2 '10 #6

Expert 100+
P: 1,221
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.

Expand|Select|Wrap|Line Numbers
  1. 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
  2. Dim dblStart As Double
  3. Dim dblLength As Double
  4. Dim ctlControl As Control
  5. Dim dblL As Double
  6. Dim dblMinWidth As Double
  7. Dim dblWidth As Double
  8.  
  9. dblMinWidth = 0.025
  10.  
  11.    On Error GoTo cswArrangeColumns_Error
  12. '        dblWidth = C1.Width
  13. '        If dblWidth = 0 Then dblWidth = dblMinWidth
  14.         dblL = C1.Left + C1.Width
  15.         C2.Left = C1.Left + C1.Width
  16.         dblL = C2.Width + dblL
  17.         C3.Left = C2.Left + C2.Width
  18.         dblL = C3.Width + dblL
  19.         C4.Left = C3.Left + C3.Width
  20.         dblL = C4.Width + dblL
  21.         C5.Left = C4.Left + C4.Width
  22.         dblL = C5.Width + dblL
  23.         C6.Left = C5.Left + C5.Width
  24.         dblL = C6.Width + dblL
  25.         C7.Left = C6.Left + C6.Width
  26.         dblL = C7.Width + dblL
  27.         C8.Left = C7.Left + C7.Width
  28.         dblL = C8.Width + dblL
  29.         C9.Left = C8.Left + C8.Width
  30.         dblL = C9.Width + dblL
  31.         C10.Left = C9.Left + C9.Width
  32.         dblL = C10.Width + dblL
  33.         C11.Left = C10.Left + C10.Width
  34.         dblL = C11.Width + dblL
  35.         C12.Left = C11.Left + C11.Width
  36.         Dim strName As String
  37.      '   strName = C12.Name
  38.         dblL = C12.Width + dblL
  39.         C13.Left = C12.Left + C12.Width
  40.         dblL = C13.Width + dblL
  41.         C14.Left = C13.Left + C13.Width
  42.         dblL = C14.Width + dblL
  43.         C15.Left = C14.Left + C14.Width
  44.         dblL = C15.Width + dblL
  45.         C16.Left = C15.Left + C15.Width
  46.         dblL = C16.Width + dblL
  47.         C17.Left = C16.Left + C16.Width
  48.         dblL = C17.Width + dblL
  49.         C18.Left = C17.Left + C17.Width
  50.         dblL = C18.Width + dblL
  51.         C19.Left = C18.Left + C18.Width
  52.         dblL = C19.Width + dblL
  53.         C20.Left = C19.Left + C19.Width
  54.         dblL = C20.Width + dblL
  55.         C21.Left = C20.Left + C20.Width
  56.         dblL = C21.Width + dblL
  57.         C22.Left = C21.Left + C21.Width
  58.         dblL = C22.Width + dblL
  59.         C23.Left = C22.Left + C22.Width
  60.         dblL = C23.Width + dblL
  61.         C24.Left = C23.Left + C23.Width
  62.         dblL = C24.Width + dblL
  63.         C25.Left = C24.Left + C24.Width
  64.         dblL = C25.Width + dblL
  65.         C26.Left = C25.Left + C25.Width
  66.         dblL = C26.Width + dblL
  67.         C27.Left = C26.Left + C26.Width
  68.         dblL = C27.Width + dblL
  69.         C28.Left = C27.Left + C27.Width
  70.         dblL = C28.Width + dblL
  71.         C29.Left = C28.Left + C28.Width
  72.         dblL = C29.Width + dblL
  73.         C30.Left = C29.Left + C29.Width
  74.         dblL = C30.Width + dblL
  75. Exit_cswArrangeColumns:
  76.     cswArrangeColumns = dblL
  77.     On Error GoTo 0
  78.    Exit Function
  79.  
  80. cswArrangeColumns_Error:
  81.     GoTo Exit_cswArrangeColumns
  82.  
Mar 2 '10 #7

Expert 100+
P: 1,221
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
Expand|Select|Wrap|Line Numbers
  1. dim intWidth as integer
  2.        c2.left = c1.left       ' added in case c2 is invisible
  3.        intWidth = c1.width
  4.        if c2.visible then    ' add this to ea object to elim gaps for invisibles
  5.                                  ' only change col2's position if it is visible
  6.          C2.Left = C1.Left + intWidth ' changed to intWidth from C1.width
  7.          intWidth = c2.width      ' add this to ea obj 
  8.          dblL = C2.Width + dblL  ' only increase add to the line's length if it is visible
  9.        endif 
  10.  
Then repeat that for all remaining columns. Really would be better done as an array with a loop.
Mar 2 '10 #8

Expert 100+
P: 1,221
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.
Mar 2 '10 #9

P: 33
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
Mar 4 '10 #10

P: 33
Thanks Jim. It is working fine. Appreciate your help.

Stan
Mar 8 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.