Date not matching data on bottom axis.
Posted: Tue Jan 08, 2013 10:36 pm
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?
Here is my VB sub (a bit sloppy) that generates the charts:
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 14406 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