Discussion Locked

date criteria with crosstab queries

0 helpful
1 reply
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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??

1 reply

by stefano

I think your FORMAT string "mmm"" '""yy" is not correct: I don't know which format date you want, but you should use something like "mmm yy". I think Access doesn't like your format function into GROUP BY statement. Try the same query without formatfunctions or using "mm/dd" format string so you can realize where is the error.

This discussion is closed to new replies.