Graph Chart with Dates Ghost Dates Access 2000

[sxschech]

It's been a while since I created charts (back in Access 97)and this is the first time I have done it in Access 2000. I set up a chart using a query that contains two fields,
Activity_Date and Amount
02-Feb-2004 50
05-Feb-2004 27
06-Feb-2004 32
10-Feb-2004 40

The Chart Source SQL Statement is
SELECT Activity_Date, Amount FROM qryActivity

This result puts in dates that don't exist to fill in the gaps between 02-Feb and 05-Feb as well as between 06-Feb and 10-Feb. We don't want this, all we want are the exact dates we have data for. I remember encountering this problem in Excel and the workaround I used there was to change the date format to Text. I tried that in Access and it didn't work. I then put a letter before the date as in D04-Feb-2004 and this worked, however, I don't want the D to display on the chart. When I tried a mid function, it reverted to filling in the gaps. Any suggestions would be greatly appreciated.

 

[dhookom]

I am fairly sure that you will not get data points plotted for dates that are not in your query. If you chart is a vertical bar graph, you would not get data values for Feb 3 and 4. What makes you think the data is included?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sxschech]

I wanted to include a screenshot to show what I was talking about, but don't know how. Perhaps I could send as a pdf attachment to your email or if you know some other way I can display the chart?

 

[sxschech]

Good news I finally found the spot where to fix the problem.

I right clicked on the chart,
chose Chart Object-->Open and then went to the
Chart Menu and then to
Chart Options then to
Axes tab and changed
Category X Axis to Category

Seems the chart defaults to Automatic and that is what was causing the chart to fill in the gaps with the non existing dates.

 

[dhookom]

Describe your chart. Are all of the dates included at the bottom (x scale) of your chart? Are there points for every date (even those not in the data)? Is your chart a line chart with a single straight line between two non-consecutive dates?
Does it look like this:

[COLOR=red]
        +     +
       / \   /       /   \ /     ---+     +     +
                                                         +--+[/color]
mth  2  2  2  2  2  2  2
     /  /  /  /  /  /  /
day  0  0  0  0  1  1  1
     2  5  6  8  0  1  3

Duane
MS Access MVP
Find out how to get great answers faq219-2884.