title: Access 2007: Hide Data Labels on Chart Object via vba with 0 values?
body: <p>I have a form in Access 2007 with a Stacked Bar Chart Object that is dynamically generated depending on the current date and outputs a PDF of the chart.</p>
  
  <p>Everything generates and works fine, but what is happening is data labels are being applied even for series with a Null or 0 value. This leads to a mess of text in various places.</p>
  
  <p>I'm looking for a way via VBA to remove any labels that belong to a series with no values.</p>
  
  <p>I've tried ruling out null values from the SQL query and also setting the format options so the 0 values won't show. I have tried looping through the series and applying a label if the value is > 0, but if I set it to apply the series name it still puts it for blank values.</p>
  
  <p><img src="https://i.imgur.com/l9Gf0Eu.png" alt="enter image description here"></p>
  
  <p>EDIT Current Code:</p>
  
  <pre><code>Option Compare Database
  
  Private Sub Form_Load()
  Dim tstChart As Graph.Chart
  
     On Error GoTo Form_Load_Error
  
  Set tstChart = [Forms]!testing!barEquip.Object
  With tstChart
      .HasTitle = True
      .ChartTitle.Font.Size = 14
      .ChartTitle.Text = VBA.Strings.MonthName(VBA.DatePart("m", VBA.Date()) - 1) &amp; " " &amp;     VBA.DatePart("yyyy", VBA.Date()) &amp; _
                          " Test Title"
  
      For Each srs In .SeriesCollection
          For Each pt In srs.Points
              pt.DataLabel.Text = "Y"
          Next
      Next
  End With
  
     On Error GoTo 0
     Exit Sub
  
  Form_Load_Error:
  
      MsgBox "Error " &amp; Err.Number &amp; " (" &amp; Err.Description &amp; ") in procedure Form_Load of VBA Document Form_testing"
  
  End Sub
  </code></pre>
  
  <p>I'm able to change each label, but I can't seem to figure out a way to check each point in the series points.</p>
  
  <p>EDIT:
  <strong>SOLVED</strong>
  (Simple, but works fine)</p>
  
  <pre><code>Sub AdjustDataLabels(cht As Chart)
  
  Dim srs As Series
  Dim pt As Point
  Dim vals As Variant
  
  For Each srs In cht.SeriesCollection
      'Apply Value labels
      srs.ApplyDataLabels (xlDataLabelsShowValue)
  
      For Each pt In srs.Points
          'Check for empty labels
          If pt.DataLabel.Text = "" Then
             'Do nothing
          Else
             'Add Series Name then remove Value
             pt.DataLabel.ShowSeriesName = True
             pt.DataLabel.ShowValue = False
          End If
      Next
  Next
  End Sub
  </code></pre>