Chart / Graph scales - How to fix sensible values on the X axis

My Access runtime application includes plotting a graph from a table where the X axis is a date/time field with a variable time interval. The report can cover anything from a few hours up to several months, and from 2 to 2000+ records, so the AutoScaling feature is essential - and works well.

However, the axis label values that Access selects from the table nearly always look stupid, because say the range is 15/01/2004 15:54 to 18/01/2004 12:08 (which is 4096 minutes), Access will print 18 labels starting at the first record and then every 228 minutes (nearest 18th of 4096),  eg 15/01/2004 15:54, 15/01/2004 19:42, 15/01/2004 23:30 etc. which is hardly easy to follow!  A human would have chosen 4 hour gaps, and then plotted values at 16:00, 20:00, 24:00 etc.

(By the way, the number of labels you get seems to depend only on the size of font selected - the bigger the font, the less labels)

So, what I  need is a way to stipulate the first value label (15/01/2004 16:00 in this example), and the gap (240 mins), whilst still graphing the full set of results as now.

Thanks for any help.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


To ensure that your chart prints the first record on a particular date, make sure that the date field (bound text box) appears somewhere on your form or report (if you do not want the date visible set the text box properties - Visible No).

To set the x axis, double click on the chart in design view, click on the x axis to select it, right click and select format axis. Go to scale tab and set according to your requirement.

best regards,

I'm assuming you use MS-Chart to create the chart.
Make sure that MS-Chart knows that you are working with a time scale (under "Chart / Options")
Try changing some of the scaling options to see if that gives you the result you want.

Hint: copy some actual data from the source query to MS-Chart, so that it makes sense... :)

If you need to change the various options by program, it is useful to know that the data model of MS-Chart is largely (or completely?) compatible with Excel. You can thus use Excel's macro recorder to learn how to program for Chart as well.

For example, I just changed dynamically the minimum scale of an embedded chart with:

       Me.OLEUnbound0.Object.Axes(1).MinimumScale = CLng(#1/2/2000#)

Try also .BaseUnit and .MajorUnit
Again, it is best to rebuild your chart in Excel, and then record all the changes you need for the scale. Then it will be easy to program that in Access.

Then you will discover that the logic behind setting a scale factor, a minimal value and a unit is far from trivial...

Lots of fun!
pauldownhamAuthor Commented:

Thanks for your reply ... but I don't think I made my question clear enough!

The chart is printing fine, and covers all the records as intended, its just that the labels on the horizontal axis (time line), whilst perfectly correct, look silly because Access has chosen the values  automatically and I would prefer to specify them at more obvious places. For example, Access takes the first record's date and time as the first label (which is fair enough!), but a human wouldn't normally choose "15/01/2004 15:54" as a good label, nor that the next one should be 228 minutes later. For clarity, I would much prefer "15/01/2004 16:00" as the first label, and the others at 4hr intervals.

I can't use fixed scaling (because the results to be plotted are so variable), so I was looking for any tips on how to somehow force sensible values as the labels. It may be that I don't let MsGraph work them out at all, but somehow print values derived from calculations on the side?

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pauldownhamAuthor Commented:

Thanks for your suggestion.

My application is a runtime Access program intended for later distribution, so I can't use Excel as an alternative to the inbuilt MSGraph, although I appreciate you are mostly suggesting this as a test arena.

I will look at your code, but presumably this is just the same as fiddling with the options within the setup screen? As per my comment above, I suspect the solution may have to be found as a way to replace the standard values?


Sorry for the misunderstanding.

If you are wanting to round the data to, say, nearest 4 hours you could add calculated fields to your base query so that they calculate the data between defined intervals (if the time is > 20:00 and < 24:00 sum the total number of labels printed).

Please advise if I am on the right track. If you advise that I am on the right track I will begin to tinker with the code to determine the values in the calculated field.

Needless to say, the calculated fields would provide the base data for your chart.


pauldownhamAuthor Commented:

I'm not sure if you are on the right track!

The example I've given is just an example. As per the original question, the graph is plotting results which can be anything from 2 records to 2000+, and can span a short time (in which case the labels may only be minutes apart), or up to a year (in which case they may be weeks apart). As a human labelling up the graph, you could always choose sensible labels to suit each occasion, even where some of these may not be data points:-

As another example, say the data points are (and I'll only do the times for clarity):

23:57, 00:03, 00:09; 00:15 .... (and then another 100 at 6 minute intervals).

A human would probably choose labels at half hour intervals (=20 labels), and start the axis labelling at 00:00. The first point plotted would still be 23:57, and all 104 results would still be plotted, but there would be an easy to follow timescale (00:00, 00:30, 01:00 ... ) printed along the axis.

With Access doing the labelling, you would likely get:

23:57, 00:32, 01:07, etc. (start at first, and then 35min gaps (18th of 624)).

which is an impossible timescale to follow!

I made a few tests:

With MS-Chart I never got quite that behavior. The value 240 minutes is possible (4 hours, 1/6th of a day), but in that case the labels are also rounded to the next 6th of a day (i.e. 0:00, 4:00, 8:00, etc.). I found no way to have the chart start with a label of 12:54, for instance. How did you do it?

Once in Chart, what are the options in "Chart / Chart Options", tab "Axes"?
What is the base chart type?
When you double-click the time-axis, tab "Scale", are there any options NOT set to Auto?

It should really work better. We might have to resort to VB coding, but I hope not!

Chart can compute "time-scales" only for line charts and XY (scatter) charts. If you have a bar chart, the time is never actually read, it is directly used for the bar labels.

Is your DATA actually always 18 points at regular intervals???
pauldownhamAuthor Commented:

You may find the comments to Ian useful.

I'm using MSGraph (is that the same as MSChart?), and have the following X axis options:

Patterns Tab:
Lines = automatic
major tick = outside
minor tick = none
tick mark = next to axis

Scale Tab:
Value Y axis crosses = 1
Number of categories between tick mark labels = 10
number of categories between tick marks = 100
(all other boxes left unticked)

Font Tab:
Arial, Regular, 5.75
Autoscale = ticked

Number Tab:
custom, dd/mm/yyyy hh:mm

Alignment Tab:
0 degrees, context
Offset = 100

Ok, we have an answer...

When you write "Number of categories" as any option of the scale tab, it means that you have categories... :) In other words, MSGraph never computes anything. It will simply take the date/time value you provide and stick it below the value.

If you use a line-chart, you should really check "Chart / Chart Options" and try to select "time-scale". The chart will be more accurate and you will have a true axis scale management.

If you use a bar-chart or the like, it's entirely different and it might become very complex in a very short time... But we're up to it!

pauldownhamAuthor Commented:


My graph is a simple line chart, and I've tried your suggestion of selecting time-scale rather than category ... and hey presto ... sensible labels. BUT, (thats a very big "but"), the graph itself has gone completely stupid!!

It seems the smallest unit of time selectable is Days, so my nicely curving chart with plots every 6 minutes has become a series of straight lines with all the results bunched together into days.

I think if we can change this to minutes we would be there for the example data ... except of course this selection needs to be automatic depending on the time spread of the records in each case.


Ok, then go with "XY (scatter)". It will look exactly like Line chart if your data is sorted, but without the "base unit" feature. It will display correctly even seconds.
The downside is that it has no "month" and "year" tick marks, always an integer number of days, so that the time axis over wide time spans will again become less elegant.

Glad I could help.
pauldownhamAuthor Commented:

We're getting close!

The best looking graph is "XY scatter data points connected by lines", and the date/time labels are looking good - but still a problem with the X axis!

There are big unplotted regions at the left and right of the plot area because the system has chosen dates to start and finish on that are outside the range of the records. Looking inside the axis properties, all the Auto boxes are ticked, but the values are strange - eg 38001.6 as the minimum (I would have expected a date/time), and on the Chart Options/Axes all the options are greyed out with X set on automatic (rather than time-scale where we left it!).

Thanks for your help
Yes, I can explain this.

The XY scatter needs two numerical values, x and y, for each point. Thus, everything happens just as if the function CLng was used on the data.
From Access, open the Immediate Pane / Debug Window and try the following:
? CLng(Date)
? CLng(# 1 apr 4:12 #)
and of course
? CDate(38001.6)
to understand the reading.

The last major improvement of MS-Graph (an of graphics in Excel) dates back to the version 5. At that time, there was an important demand for financial graphs, so that the automatic treatment of dates for Line Charts has been added. Alas, the scientists never put enough pressure on MS to get some of the most trivial enhancements... This is why we are left with this largely unuseable system.
(end of flames)

Access will choose the previous Major Unit mark for the leftmost date/time and the next Major Unit mark for the rightmost. This allows the labels to correspond to something useable and to fall on logical times (e.g. noon).

You can force narrower margins by forcing a smaller major unit. If you need that, you can use some VB instruction like in my very first comment to adjust only that.

I would suggest dragging the borders of the plot areay so that it fills the maximal available space and leave it at that, but it's your call.

Tell me if you need more help.
pauldownhamAuthor Commented:

This is looking good  ... just need some ideas on code now I think.

I've experimented with the CLng function, and can produce a perfect graph by unticking the auto boxes in the X Axis panel and typing in my own worked out numerical values. I reckon I can also work out these values for any set of data (and make them part of the data), so what I need next is some wonderful code that writes the calculated numbers into the X Axis boxes at each time of plotting.

As an overview, this graph is part of a runtime package for distribution to people whose printers I won't know about, so I'm already sacrificing vast areas of my page to huge borders so that it will always fit their paper settings - so to loose yet more to blank sections within the plot area is not an attractive option.

Thanks again
For a MS-Graph chart called uofChart on the detail section, you can use...

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

    With uofChart.Object.Axes(1)
        .MinimumScale = CDbl(#1/1/2004 12:00:00 PM#)
        .MaximumScale = CDbl(#1/2/2004 6:00:00 PM#)
        .MajorUnit = CDbl(#4:48:00 AM#)   ' or 1/5
        .TickLabels.NumberFormat = "d h:mm"
    End With

End Sub

Of course, the values can be calculated elswhere, or even on the report itself...

Have Fun!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pauldownhamAuthor Commented:
Sorry, I'm being thick here!

Where do you find out what the chart is called (your example is uofChart), is it the Unbound Object Frame name (from the pop up properties you see when right clicking the chart)?

Also, where does the code go ... I'm only used to entering these as Event Procedures on forms?

Thanks again

PS Your 500 points are nearly ready!!
The code is a "Private Sub Detail_Print", or the Print event of the Detail section. So it is just another event procedure... If you chart is on another section, use that section's print event.
And yes "uofChart" is the name of the "unbound object frame" containing the MS-Graph object, you got that right :)

Good Luck
pauldownhamAuthor Commented:

This technique seems to do the job ... so I'm now working on some fancy formulea to get nice looking labels and gaps for all the likely cases that might arise in our data.

Its good to be in control!

Thanks for your help, and the points are awarded.  

(You only got a GOOD, because I'm otherwise giving out too many EXCELLENTS!)


> You only got a GOOD

I consider that rude :)

I am used to getting only EXCELLENTS, this HURTS! :)

Good luck with your charting...
pauldownhamAuthor Commented:

I wouldn't want you to feel hurt, but I don't think I can change grades after the event!

So, here's a secret plan:

There's just one more thing to resolve on this issue - which is a separate question I'm going to post in a few minutes - and if you get that answered straightaway, not only will you get more points, but also an excellent!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.