Time based chart not displaying correctly (1 Viewer)

[tmd_63]

I have a table with three columns and 2000+ records.
The columns are 'Date & Time' 'kVarh' and 'kWh'. The date & time are for half hour periods.
I have tried to create a chart to show the kWh useage for a selected range (using two calender controls) but either the order comes out wrong (26/6 12am is next to 26/6 12pm then 27/7 12am etc) or the values are bunched into days on the x-axis.
I am running Access 2000 (company standard).
How can I get the chart to display the time frame correctly?

 

[The_Doc_Man]

Odds are you are doing this as a bar-chart AND/OR the timestamp is text-formatted.

First, build a query that includes a CDate([timestamp]) to get the date/time into a usable format. Bring over your other fields with conversion to a numeric form if they are not already so.

Next, build your chart from the query, not the table.

I would avoid using bar-chart format when dealing with time because of what a bar chart REALLY means. (It is treating time not as time but as an arbitrary category label for segregation and counting by categories.) I always plot time-based charts as X-Y graphs and then diddle the X-axis labeling to reinterpret the time.

Sometimes I take it to the next level by doing not CDate(x) but CDbl(Cdate(x)) and plotting THAT as my X-axis. Here's the helpful "secret" - Access will STILL interpret that correctly when you dink with the units of the major tick-mark labels.

Your real problem isn't really Access per se; it is the MS Access/MS Graph interface, which Access often gets wrong. Excel more often gets it right, in my experience. But you are using Access so let's stay focused.

When you get your data plotted, Access and Graph will "optimize" the chart for you such that you will probably have to adjust the range of either axis. Just remember that the time units for Access date/time fields and their equivalents will be DAYS. So if the chart starts too early or late, adjust the axis as though it was in units of days. To make the chart start five days earlier, subtract 5 from the starting point. To make the chart end two days later, recompute the range to add 2 to it.

Yeah, it's a pain in the toches to get Access to graph just like you want it to, but it IS do-able. Be patient because you are dealing with a cranky interface.

 

[tmd_63]

I tried to do this (Note: I was only using either X-Y or Line charts). I created a query before and used this as my basis for the chart. But I tried again using the CDate(), on building the chart (using the insert chart from the menu) as an X-Y chart, the system changed my date-time column using FORMAT into a string and then dumped the whole entry as 1st Qtr, 2nd Qtr etc on the chart. This is completely wrong (and I had told the wizard that I wanted the display as hours not quarter-years).
I am getting very frustrated with access and I am thinking of putting the data into Excel instead, But here the problem is the shear number of records involved (one per half hour for every day over a year or more).
The reasons for using Access was to allow the viewing of sections of the data via two calender controls to display a day or weeks worth of data selected from the full data set which Excel does not provide easy control over.

 

[tmd_63]

OK. I am still having a problem with the use of a chart control and two calender controls on a form.
Can anyone shed any light on how to do this in Access 2000 please.

 

[tmd_63]

Can I assume, by the lack of response to my query, that Microsoft just will not work correctly for this type of problem.

I have now found an alternative. I use date converted to a double number. Then change the axis to display a date.

 

[AccessV2.0]

Odds are you are doing this as a bar-chart AND/OR the timestamp is text-formatted.

First, build a query that includes a CDate([timestamp]) to get the date/time into a usable format. Bring over your other fields with conversion to a numeric form if they are not already so.

Next, build your chart from the query, not the table.

I would avoid using bar-chart format when dealing with time because of what a bar chart REALLY means. (It is treating time not as time but as an arbitrary category label for segregation and counting by categories.) I always plot time-based charts as X-Y graphs and then diddle the X-axis labeling to reinterpret the time.

Sometimes I take it to the next level by doing not CDate(x) but CDbl(Cdate(x)) and plotting THAT as my X-axis. Here's the helpful "secret" - Access will STILL interpret that correctly when you dink with the units of the major tick-mark labels.

Your real problem isn't really Access per se; it is the MS Access/MS Graph interface, which Access often gets wrong. Excel more often gets it right, in my experience. But you are using Access so let's stay focused.

When you get your data plotted, Access and Graph will "optimize" the chart for you such that you will probably have to adjust the range of either axis. Just remember that the time units for Access date/time fields and their equivalents will be DAYS. So if the chart starts too early or late, adjust the axis as though it was in units of days. To make the chart start five days earlier, subtract 5 from the starting point. To make the chart end two days later, recompute the range to add 2 to it.

Yeah, it's a pain in the toches to get Access to graph just like you want it to, but it IS do-able. Be patient because you are dealing with a cranky interface.

I have formatted the date using CDATE, but the graph continues to put my dates at 1/1/00; It clearly recognizes my dates as a value of 1. What else can I try?