Chart Scale

[Guest]

I wonder if anyone can help me in solving what initially appeared to be a
very easy task. I have a Line Graph with X Axis showing "Days Post
Randomisation" and Y Axis showing "Result".
The values on X axis (and Y) are not similar and vary from record to record.
All I want to do is to create an X axis which is fixed (-10 to 100) for ALL
the records. I have tried to change the scale in X axis properties specifying
a Custom Min as -10 and a Custom Max as 100. The chart is not adjusted as
desired and access interprets it differently. It adds blank areas at the
start (10 spaces) and at end (100 spaces). The data is plotted in whatever
small space is left giving a very congested plot.

Please help!
I am using Access 2003 and the DB is in 2000 format

 

[Van T. Dinh]

This is the code I used to set the custom Min & Max value for the Y-Axis so
it won't work directly in your case. However, you may be able to set the
X-Axis similarly by picking out the MSGraphs Objects & Properties and check
the ObjectBrowser for other MSGraphs Objects & Properties which are related
to the X-Axis.

You will need to add the "Microsoft Graph 11.0 Object Library" to the
References Collection of your database.
--
HTH
Van T. Dinh
MVP (Access)



***Sample code****
Private Sub Form_Load()
'================
' Form_frmGraph_LL.Form_Load
'--------
' Purpose:
'--------
' Notes :
'--------
' Parameters:
'
'--------
' Called Subs/Functions
' (none)
'--------
' Calling Subs/Functions
' (none)
'--------
' Returns:
' (none)
'--------
' Author : Van T. Dinh, Monday, 16 June 2003
'--------
' Revision History
' Monday, 16 June 2003 (VTD):
'================
On Error GoTo Form_Load_Err
Const QUERYNAME As String = "qqptGraph"

Dim objChart As Graph.Chart
Dim dblChart_Min As Double
Dim dblChart_Max As Double
Dim dblChart_Range As Double
Dim dblChart_ExRange As Double

Dim dblMin_RMin As Double
Dim dblMin_RMax As Double
Dim dblMin_SMin As Double
Dim dblMin_SMax As Double
Dim dblMax_RMin As Double
Dim dblMax_RMax As Double
Dim dblMax_SMin As Double
Dim dblMax_SMax As Double

Set objChart = Me.oleGraph.Object
With objChart
' Show Chart Title if available
If Len(Trim(Me.OpenArgs & "")) > 3 Then
.HasTitle = True
.ChartTitle.Text = Nz(Me.OpenArgs, " ")
Else
.HasTitle = False
End If

' Set Chart Min, Max, (extended) Range
If DCount("*", QUERYNAME) > 0 Then
dblMin_RMin = Nz(DMin("[Result Min]", QUERYNAME, "[Result Min] Is Not
Null"), 0)
dblMin_RMax = Nz(DMin("[Result Max]", QUERYNAME, "[Result Max] Is Not
Null"), 0)
dblMin_SMin = Nz(DMin("[Spec Min]", QUERYNAME, "[Spec Min] Is Not
Null"), 0)
dblMin_SMax = Nz(DMin("[Spec Max]", QUERYNAME, "[Spec Max] Is Not
Null"), 0)
dblMax_RMin = Nz(DMax("[Result Min]", QUERYNAME, "[Result Min] Is Not
Null"), 0)
dblMax_RMax = Nz(DMax("[Result Max]", QUERYNAME, "[Result Max] Is Not
Null"), 0)
dblMax_SMin = Nz(DMax("[Spec Min]", QUERYNAME, "[Spec Min] Is Not
Null"), 0)
dblMax_SMax = Nz(DMax("[Spec Max]", QUERYNAME, "[Spec Max] Is Not
Null"), 0)
dblChart_Min = fnZeroIgnoredMin(dblMin_RMin, dblMin_RMax, dblMin_SMin,
dblMin_SMax, _
dblMax_RMin, dblMax_RMax, dblMax_SMin,
dblMax_SMax)
dblChart_Max = fnZeroIgnoredMax(dblMin_RMin, dblMin_RMax, dblMin_SMin,
dblMin_SMax, _
dblMax_RMin, dblMax_RMax, dblMax_SMin,
dblMax_SMax)
dblChart_Range = Abs(dblChart_Max - dblChart_Min)

dblChart_Min = Round(dblChart_Min - 0.1 * dblChart_Range, 1)
dblChart_Max = Round(dblChart_Max + 0.1 * dblChart_Range, 1)
If (Abs(dblChart_Max - dblChart_Min) <= 4# * 10 ^ -2) Then
dblChart_Max = dblChart_Min + 0.1
End If
dblChart_ExRange = dblChart_Max - dblChart_Min

' Set Chart Min, Max, (extended) Range, MinScale, MaxScale & MajorUnit
.Axes(Graph.xlValue).MinimumScale = dblChart_Min
.Axes(Graph.xlValue).MaximumScale = dblChart_Max

If dblChart_ExRange > 4# Then
.Axes(Graph.xlValue).MajorUnit = Round((dblChart_Max - dblChart_Min)
/ 4, 0)
ElseIf dblChart_ExRange > 0.4 Then
.Axes(Graph.xlValue).MajorUnit = Round((dblChart_Max - dblChart_Min)
/ 4, 1)
Else
.Axes(Graph.xlValue).MajorUnit = Round((dblChart_Max - dblChart_Min)
/ 4, 2)
End If

' Set axes crossing
.Axes(Graph.xlValue).Crosses = Graph.XlAxisCrosses.xlAxisCrossesCustom
.Axes(Graph.xlValue).CrossesAt = dblChart_Min
.Axes(Graph.xlCategory).Crosses = 1
End If
End With
Set objChart = Nothing

Form_Load_Exit:
Exit Sub

Form_Load_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf &
vbCrLf & _
"(Programmer's note: Form_frmGraph_LL.Form_Load)" & vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume Form_Load_Exit
End Sub
****Code ends****