I am charting data from a query that consists of a Date/Time field and
a Number field. The Date/Time field is the x scale on my chart.
Sometimes data is collected from different times during the one day.
I need to be able to see these times as different time points on the x
scale of my chart. To accomplish this I am using the following
statement as the row source for my chart: "SELECT
(Format([Date],"DDDDD HAMPM")),Max([Baro]) AS [Baro1] FROM [Baro]
GROUP BY (Int([Date]*24)),(Format([Date],"DDDDD HAMPM"));"
I am using the custom number format "d/mm/yyyy h:mm" for the x scale.
I have the "minimum", "maximum", "major unit" and "minor unit" set to
auto.
The different data points during a single day display correctly on the
chart, but despite trying everything that I can think of, I cannot
make the x scale Date/Time values display correctly. They are
displaying what seems to be design data e.g. "1/1/1990 00:00" etc. I
have tried other date formats but they all have a similar problem and
do not display correctly.
If I remove the "HAMPM" from the charts rowsource, the dates display
fine, but obviously the chart does not differentiate between different
times in a given day along the x axis as I require. There appears to
be some conflict with telling the chart that I want to see hourly data
by including the "HAMPM" part of the rowsource statement and the
format of the x axis date information. Even a simple "Date" format
does not display correctly.
Have I missed something obvious? I have charted the same data in
Excel and it looks fine. The big difference with an Excel chart is
that it doesn't rely on the rowsource statement in the same way that
an Access chart does.
If anyone can shed any light on this, it would be greatly appreciated.