Custom Data Label Using .Points(i).DataLabel.Text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm so close! This will be HUGE if someone has a clever way of doing this. I
have a graph that shows sales data. The date is on the x axis and sales are
plotted on a line graph. Pretty standard graph. The source data is a table
called Shipments. It has two fields: DayDesc which is a short data value, and
Sales which is a number value.

Here's where it gets exciting! I have a second table called Events that has
two columns as well. The first column is DayDesc which is a short data value,
and the second column is called Event which is text field. What I'm hoping to
do is insert the Event value as a DataLabel whenever the DayDesc in the
Shipments table matches the DayDesc in the Event table.

The code below works but it doesn't add the event. I think I need some way
of seeing if the DayDesc fields match and, if they do, pull the Event field
into the DataLabel. Any help would be great. Thanks!!

Dim cht As Graph.Chart
Dim chtSeries As Graph.Series
Dim chtLabel As Graph.DataLabel
Set cht = Me.Chart.Object

With cht.SeriesCollection(1)
For i = 1 To .Points.Count
If .Points(i).DataLabel.Text Like "Amber*" Then 'MATCH DAYDESC HERE?
.Points(i).DataLabel.Text = "Test" 'PUT EVENT HERE
Else
.Points(i).DataLabel.Text = ""
End If
Next i
End With
 
I don't know why I'm thinking of this at 11PM on a Friday, but here goes...I
think what might work is something where I use a DLOOKUP using the x axis
value (DayDesc) to get the Event value in the Event table. Something like:

For i = 1 To .Points.Count
Nz(DLOOKUP("[Event]","[Events]","[DayDesc]= x axis value"),"")
Next i

I'm not sure what the code is for getting that x axis value. Anyone know?
Thanks!!
 
Back
Top