CODEWITHSUNDEEP
ms-access
ChiliYago
ChiliYago

Reputation: 12289

MS Access Chart Showing Dates Not Found in Dataset

The following chart is showing dates that are not found in the underlying dataset. Below are images of the chart, the SQL statement used to populate the chart and a gridview of the SQL statement. How does one configure the chart to only show dates that are in the dataset. I don't want to show Saturday and Sunday.

TRANSFORM Sum([Hours]) AS [SumOfHrs] 
SELECT (cstr(Format([ReportDate],"DDDDD"))) FROM [2013_4_30_TimeTrack] 
WHERE ((([2013_4_30_TimeTrack].CreatedBy)="Tim Odell")) 
GROUP BY (Int([ReportDate])),(Format([ReportDate],"DDDDD")) 
PIVOT [ProjectName];

enter image description here

enter image description here

Upvotes: 2

Views: 1084

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

One possibility is that if the chart control knows that the x-axis column is Date/Time then it might try to be "helpful" and fill in the missing dates for you. If that is the case then you could try tweaking your query so that column is Text (e.g., by using Format([DateField], "Short Date") and see if that works better for you.

Edit

Try this: Open the report in Design View and double-click the chart control. From the Microsoft Graph menu bar, choose Chart > Chart Options..., then on the "Axes" tab select "Category" for the "Category (X) axis".

category.png

Upvotes: 3

Related Questions