Custom formatted date in ctab query (A2K)

Topic

New Reply

Rows and column headings are date fields formatted “mm/yyyy” in the crosstab query. Spans two separate years.

How to force row and column headings to sort chronologically not alpha in the crosstab query?

Looking for ideas?

TIA.

Reply | Quote

Replies

Use the ORDER BY clause to sort the rows.
You could write some VBA code to setup the PIVOT clause for the sorting of the columns.
Pat

Reply | Quote

You would have to use Column Headings property (Query Properties dialog) to specify sort order for the column headings since the Format function results in a text string. In SQL these values will equate to the values listed in the optional IN clause following the PIVOT clause. Example:

TRANSFORM Nz(Count([OrderID]),0) AS [Orders Count]
SELECT Orders.CustomerID
FROM Orders
GROUP BY Orders.CustomerID
PIVOT Format([OrderDate],”mm/yyyy”) In (“01/1997″,”02/1997″,”03/1997″,”04/1997″,”05/1997″,”06/1997”)

This example from NorthWind database. If you don’t want to manually enter the fixed column headings or they may often change, you can use VBA to generate the SQL statement dynamically.

HTH

Reply | Quote

Thanks Mark

I understand the SQL; thanks for the detail.

I cheated, in the QBE multiplied year by 100 and added month added as row value in ctab so there is something in the query I do not want,
plus used column headings.

The SQL is what you posted.

Thanks a million.

Reply | Quote

A while back I posted some code that showed how to set crosstab column headings dynamically, but that code would not work in this case because the formatted date column headings make things a bit more convoluted. The attached sample database, using revised code, demonstrates how this can be done using VBA to dynamically create a new crosstab query with formatted date column headings sorted in chronological, not alphabetical order. Date range for query based on user input. This example uses the Orders table from NorthWind.mdb for demonstration purposes. To test open frmXTAB form, all code is in form module. (It was too lengthy to post here.) If using this code in your own project, make sure to set reference to “Microsoft DAO 3.6 Object Library.”

HTH

Reply | Quote