Date not matching data on bottom axis.

[n84_All]

Date not matching data on bottom axis.
Why is this happening? It's like the bottom axis is offset. Am I missing something?
Also, I would like to have each bar shown at the beginning of each month's tick and only one tick per month. How do I accomplish this?

1.png (63.82 KiB) Viewed 14375 times

Here is my VB sub (a bit sloppy) that generates the charts:

Code: Select all

Private Sub BuildGraph(strSQL, field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, header, cumulative, graphtype)
    Set gdb = CurrentDb()
    Dim rs As Recordset
    Dim ppf1, ppf2, ppf3, ppf4, ppf5, ppf6, ppf7, ppf8, ppf9, ppf10
    If cumulative Then
        strSQL = Replace(strSQL, " Desc", "")
    End If
    Set rs = gdb.OpenRecordset(strSQL, dbOpenDynaset)
    rs.MoveFirst
    cont = 0
    
    TChart1.ClearChart
    'TChart1.Panel.Gradient.Visible = True
    TChart1.Aspect.View3D = False
    TChart1.header.Text.Add header
    'TChart1.AddSeries scBar3D
    TChart1.Legend.Visible = False
    TChart1.Axis.Bottom.Labels.Angle = 90
    TChart1.Axis.Bottom.Labels.DateTimeFormat = "mmm yy"
    TChart1.Axis.Bottom.increment = 5
    TChart1.Axis.Bottom.MinorTickCount = 0
    'TChart1.Series(0).Marks.Item(cont - 1).Text.Text = ppf2 & " hrs"
    TChart1.Axis.Left.Labels.ValueFormat = "# hrs"
    TChart1.Axis.Left.increment = 2
            
    With rs
        
        Do While Not .EOF

            If Not IsNull(field1) And Not IsNull(field2) And field1 <> "" And field2 <> "" Then
                
                If .fields(field2) = "" Or IsNull(.fields(field2)) Then
                    If cumulative Then
                        ppf2 = ppf2 + 0
                    Else
                        ppf2 = 0
                    End If
                Else
                    If cumulative Then
                        ppf2 = ppf2 + .fields(field2)
                    Else
                        ppf2 = .fields(field2)
                    End If
                End If
                ppf1 = CDate(.fields(field1))
                
                If IsNull(graphtype) Or graphtype = "" Then
                    TChart1.AddSeries scBar
                Else
                    TChart1.AddSeries graphtype
                End If
                
             
                TChart1.Series(0).AddXY ppf1, ppf2, "", vbBlue
                TChart1.Series(0).Marks.Visible = True
                TChart1.Series(0).XValues.DateTime = True
                TChart1.Series(0).Marks.BackColor = &HF0F0F0
                If rs.RecordCount > 20 And (graphtype = "scBar" Or IsNull(graphtype) Or graphtype = "") Then
                    TChart1.Series(0).Marks.Visible = False
                End If
                'TChart1.Series(0).Clear
            End If
            
            If Not IsNull(field3) And Not IsNull(field4) And field3 <> "" And field4 <> "" Then
                
                If .fields(field4) = "" Or IsNull(.fields(field4)) Then
                    If cumulative Then
                    ppf4 = ppf4 + 0
                    Else
                        ppf4 = 0
                    End If
                Else
                    If cumulative Then
                        ppf4 = ppf4 + .fields(field4)
                    Else
                        ppf4 = .fields(field4)
                    End If
                End If
                ppf3 = CDate(.fields(field3))
                
                If IsNull(graphtype) Or graphtype = "" Then
                    TChart1.AddSeries scBar
                Else
                    TChart1.AddSeries graphtype
                End If
                
                TChart1.Series(1).AddXY ppf3, ppf4, "", vbRed
                TChart1.Series(1).Marks.Visible = True
                TChart1.Series(1).XValues.DateTime = True
                If rs.RecordCount > 20 And (graphtype = "scBar" Or IsNull(graphtype) Or graphtype = "") Then
                    TChart1.Series(1).Marks.Visible = False
                End If
                'TChart1.Series(1).Clear
            End If

            If Not IsNull(field5) And Not IsNull(field6) And field5 <> "" And field6 <> "" Then
                
                If .fields(field6) = "" Or IsNull(.fields(field6)) Then
                    If cumulative Then
                    ppf6 = ppf6 + 0
                    Else
                        ppf6 = 0
                    End If
                Else
                    If cumulative Then
                        ppf6 = ppf6 + .fields(field6)
                    Else
                        ppf6 = .fields(field6)
                    End If
                End If
                ppf5 = CDate(.fields(field5))
                
                If IsNull(graphtype) Or graphtype = "" Then
                    TChart1.AddSeries scBar
                Else
                    TChart1.AddSeries graphtype
                End If
               
                TChart1.Series(2).AddXY ppf5, ppf6, "", vbGreen
                TChart1.Series(2).Marks.Visible = True
                TChart1.Series(2).XValues.DateTime = True
                TChart1.Series(2).Marks.BackColor = &HF0F0F0
                If rs.RecordCount > 20 And (graphtype = "scBar" Or IsNull(graphtype) Or graphtype = "") Then
                    TChart1.Series(2).Marks.Visible = False
                End If
                'TChart1.Series(2).Clear
            End If
            
            If Not IsNull(field7) And Not IsNull(field8) And field7 <> "" And field8 <> "" Then
                
                If .fields(field8) = "" Or IsNull(.fields(field8)) Then
                    If cumulative Then
                    ppf8 = ppf8 + 0
                    Else
                        ppf8 = 0
                    End If
                Else
                    If cumulative Then
                        ppf8 = ppf8 + .fields(field8)
                    Else
                        ppf8 = .fields(field8)
                    End If
                End If
                ppf7 = CDate(.fields(field7))
                
                If IsNull(graphtype) Or graphtype = "" Then
                    TChart1.AddSeries scBar
                Else
                    TChart1.AddSeries graphtype
                End If
               
                TChart1.Series(3).AddXY ppf7, ppf8, "", vbYellow
                TChart1.Series(3).Marks.Visible = True
                TChart1.Series(3).XValues.DateTime = True
                If rs.RecordCount > 20 And (graphtype = "scBar" Or IsNull(graphtype) Or graphtype = "") Then
                    TChart1.Series(3).Marks.Visible = False
                End If
                'TChart1.Series(3).Clear
            End If
            
            If Not IsNull(field9) And Not IsNull(field10) And field9 <> "" And field10 <> "" Then
                
                If .fields(field10) = "" Or IsNull(.fields(field10)) Then
                    If cumulative Then
                    ppf10 = ppf10 + 0
                    Else
                        ppf10 = 0
                    End If
                Else
                    If cumulative Then
                        ppf10 = ppf10 + .fields(field10)
                    Else
                        ppf10 = .fields(field10)
                    End If
                End If
                ppf9 = CDate(.fields(field9))
                
                If IsNull(graphtype) Or graphtype = "" Then
                    TChart1.AddSeries scBar
                Else
                    TChart1.AddSeries graphtype
                End If
                
                TChart1.Series(4).AddXY ppf9, ppf10, "", vbPurple
                TChart1.Series(4).Marks.Visible = True
                TChart1.Series(4).XValues.DateTime = True
                TChart1.Series(4).Marks.BackColor = &HF0F0F0
                If rs.RecordCount > 20 And (graphtype = "scBar" Or IsNull(graphtype) Or graphtype = "") Then
                    TChart1.Series(4).Marks.Visible = False
                End If
                'TChart1.Series(4).Clear
            End If
        
            'MsgBox field1 & ": " & ppf1 & ", " & field2 & ": " & ppf2 & ", " & field3 & ": " & ppf3

            .MoveNext
        Loop
    End With
    
    rs.Close
    Set rs = Nothing
End Sub

[Yeray]

Hi,

First of all, if you are going to have only one value for each bar series, why don't you have a unique bar series with all the values?

I'd suggest you to use talPointValue Labels Style to force to show the point values only where points are being drawn.

Code: Select all

TChart1.Axis.Bottom.Labels.Style = talPointValue

[n84_All]

I'm believe that that didn't work out. Also, I am using 5 different conditional series because this is called by other routines that have up to 5 series. Each block of <If Not IsNull(field1) And Not IsNull(field2) And field1 <> "" And field2 <> "" Then> represents one single series.

2.png (170.52 KiB) Viewed 14352 times

[Yeray]

Hi,

Then I'd try with custom labels. Find an example of how to make them work in the features demo, at "All Features\Welcome !\Axes\Labels\Custom Labels".
Try to add a label for each month with a loop.

[n84_All]

'at "All Features\Welcome !\Axes\Labels\Custom Labels"' ???

[n84_All]

I'm interested in trying the implementation of the custom label solution. I need a little more information on how the custom label API looks like tho. Thanks.

[n84_All]

Please help.

[n84_All]

Figured it out myself. By the way all that you said had nothing to do with it.

[Yeray]

Hi,

'at "All Features\Welcome !\Axes\Labels\Custom Labels"' ???

Yes. See the image:

Custom Labels.png (71.86 KiB) Viewed 14316 times

I'm interested in trying the implementation of the custom label solution. I need a little more information on how the custom label API looks like tho. Thanks.

As you'll see in the Custom Labels example above, you basically have to call the Clear function, and then add the labels you like giving the value and string to be shown.

Code: Select all

  TChart1.Axis.Bottom.Labels.Clear
  TChart1.Axis.Bottom.Labels.Add myValue, myString

Figured it out myself. By the way all that you said had nothing to do with it.

We'll be glad to hear about the solution you have found, if you think it could help other customers with a similar issue.