Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2009

    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]
    FROM qryReport
    GROUP BY qryReport.Report_Type, qryReport.TO_Number
    ORDER BY qryReport.TO_Number
    PIVOT CDate(Format([Report_Date],"mmm-yyyy"));

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

    instead of:

    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
    FROM Reports
    ORDER BY Reports.TO_Number, Reports.Report_Type, Reports.Report_Date;

    I will appreciate any help you can give.


    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts