Flexible default y-axis labels in Microsoft Graph

[Guest]

I'm using Microsoft Graph in Access 2003. I'm running into difficulty
because the y-axis scale is not changing to accommodate the data appearing in
the report. For example, if the first report requires a graph ranging from 0
to 100, subsequent reports show a range of 0 to 100, when it may be more
appropriate for them to show a range between 0 and 1.

Is there a way to set the default y-axis values to be dependent on the
source data, rather than the keyed in values on the "scale" tab of the format
axis format window?

 

[SA]

Sue:

Yes, it is possible to do this, there are two methods to do so:

1.) You can set the graph object's axis max / min values to be automatically
calc'd by Graph at run time by unchecking the min / max / floor options in
the Graph Axis dialog (double click on the axis itself) and then optionally
checking the Floor (XY plane) crosses at the minimum value

2.) Alternately you can set the values at run time using Visual Basic code
to do so.

The first step would be to determine at run time, what the min / max values
of the underlying query / recordset would be. You could do this by creating
two new queries based on your report's record source query, one which
returns the minimum value and one which returns the maximum value. You
would then open both queries as recordsets in the report's On Open event and
set the values from those queries into module level variables for the report
(i.e. create the variables in report's module general section so they can be
visible to all events in the report.)

The second step would be to set the axes values. To do so you'd add code
like this in the On Print event of the section of the report that has the
chart / graph:

Dim objGraph as Object
Dim objAxis as Object
Set objGraph = Me!NameOfYourGraphObject
Set objAxis = objGraph.Axis(2) 'Y Axis
With objAxis
.MinimumScale = YourMinimumValue
.MaximumScale = YourMaximumValue
.MajorUnitScale = SomeVale
End With

Etc. You can look up all the properties that can be set via vb by locating
the file VBAGRXX.CHM on your machine, where XX = the version of Access you
are running as in VBAGR10.CHM = the version for Access 10 (Xp).

Hope that helps

 

[Guest]

Where do you see the ability to change the the XY plane to cross at the
minimum value? I only have the options to change it to cross at the maximum
value. I am using Access 2003. The only other option is to set it to cross
at a value you enter in, but then I am back to entering it in.

I tried the second solution " Set objAxis = objGraph.Axis(2) 'Y Axis" but
this tells me this object is unavailble.

Thanks for the help, would be huge for me to make work.