When I use the chart wizard in Access 97 I do not have any problems opening a report using date criteria (between [starting date] and [ending date]) as long as I use two fields (e.g. date and incidenttype. The SQL statement for this chart looks like
SELECT (Format([IncidentDate],”mmm”” ‘””yy”)) AS Expr1, Count(Total.LocIncident) AS CountOfLocIncident
FROM Total
GROUP BY (Format([IncidentDate],”mmm”” ‘””yy”)), (Year([IncidentDate])*12+Month([IncidentDate])-1);
If I add one or more otherfields (e.g. incidentresult) I end up having crosstab queries my report does not open with the graphs for that specific time period and I get the message that the database engine does not recognize ‘starting date’ as a valid field name or expression.
The SQL statement for that chart looks like
TRANSFORM Count(*) AS Count
SELECT (Format([IncidentDate],”mmm”” ‘””yy”)) AS Expr1
FROM Total
GROUP BY (Year([IncidentDate])*12+Month([IncidentDate])-1), (Format([IncidentDate],”mmm”” ‘””yy”))
PIVOT Total.IncidentResult;
Do I need to change anything in the SQL statement or what should I do to make it work??
