Unanswered: Sorting/Formating Dates in Cross Tab Query
I am making a crosstab query where the columns are report dates. I want the columns sorted by date and the format to be mmm, yyyy. The SQL for the query is:
TRANSFORM Count(qryReport.Filed) AS CountOfFiled
SELECT qryReport.Report_Type, qryReport.TO_Number, Count(qryReport.Filed) AS [Total Of Filed]
GROUP BY qryReport.Report_Type, qryReport.TO_Number
ORDER BY qryReport.TO_Number
A partial view of the result of the crosstab query is in the attached pdf file.
The qryReport is based on a table where the dates are already formatted as mmm-yyyy. These dates show up formatted this way in a form based on the table and in the datasheet view of qryReport (based on the same table), but in the crosstab query they show up like this:
3/1/2010 4/1/2010 5/1/2010 6/1/2010 7/1/2010 ...
Mar-2010 Apr-2010 May-2010 Jun-2010 Jul-2010 ...
The SQL for qryReport is:
SELECT Reports.Report_Type, Reports.Report_Number, Reports.TO_Number, Reports.Filed, Reports.Report_Date
ORDER BY Reports.TO_Number, Reports.Report_Type, Reports.Report_Date;