How to reorder columns in a crosstab query

[Guest]

The columns in a crosstab query are generated automatically, and sorted
alphabetically, even though the field's data type is Date/time.

How do I make the query sort the columns in date order?

Thanks.

 

[Guest]

Right click in the grey porition of the query grid and select Properties,
then type the values in the order you want them in the Column Headings area.

It is imperative that you get these values exactly as they appear in the
query because if the column headings are misspelled, the corresponding column
will not show up in the query results.

Once you have done this, you should look at the SQL to see how it actually
looks in the SQL.

 

[Guest]

Does this not require you to know, in advance, what the column headings will
be (what dates will occur?) ... surely this will change whenever the
underlying data changes?

 

[Guest]

Does this not require you to know, in advance, what the column headings will
be (what dates will occur?) ... surely this will change whenever the
underlying data changes?

 

[Guest]

Absolutely correct. This technique works best for crosstabs that will be
used for fixed reports.

However, you can, and I have, created the query in VBA. If you are doing
this, then you can write a query that will return the "dates" that will be
selected in your main query. Then just sort them in the order you want them,
and add them to the PIVOT clause of the SQL.

If you google on "VBA dynamic crosstab report", you will find some
references to how to create a report with dynamic column headers.

HTH
Dale