Thorny problem graphing XAxis dates

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

Sorry, I'm not really sure how to state this question succinctly. I
have data in an MSAccess Db re medications and the time\date and
amounts taken. Mads are entered only in the db on days when they are
taken yet I need the graph to show ALL days including days not taken.
That is, days when the amount is inferred to be zero because no data
appears for that day. Sorry, that's probably as clear as mud.

Currently, I'm using VBA code to select the data, build a spreadsheet
and then build a graph from the spreadsheet I built. The problem is
that it (obviously) graphs only the days meds are taken, not days in
between.

I did something like this many years ago using brute force coding but
it is a torturous coding process and doesn't seem very elegant. I
think I may have seen a way to do a SQL select statement that
accomplishes what I want but my thinker is getting old and sometimes
unreliable.

Does anyone have any ideas on this?

Thank you,

Don in Redmond
 
Don -

You need to make sure your dates are recognized by Excel as numerical
values, not as text labels. Coming from Access, it's not unlikely that
they'd be imported as text. You can use Text To Columns to try to fix a
nonnumeric column of dates.

Then you need to make sure these dates are used as the category values.
It sounds like this is not an issue in your case.

Finally, you need to make sure that your chart has a time-scale category
axis. If the dates are recognized as numeric, Excel does this
automatically, which is why I started with my first paragraph. Fix the
dates, and the axis should take care of itself. (You'll have to
specifically change the axis of an existing chart that was first made
with text labels.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
If I understand you correctly, after you have created the chart, select
it, then select Chart | Chart Options... | Axes tab. In there make sure
the x-axis type is 'Timescale'.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005
 
you need to make sure that your chart has a time-scale category
axis.
Click to expand...

Thanks Jon, it's exactly what I need. My data was of the correct data
type, but I did not select 'Time Scale' for the XAxis.

Regards,

Don
 
Thanks Tushar, you understand me perfectly correctly and your
suggestion is right on.

Thank you,

Don
 
Back
Top