Sorting Dates in crosstab queries

J

John Baker

How can I sort by date in a crosstab query?
The date field is the column heading of the crosstab query and they can
change each time the query is run. The columns are grouped by day.

Any help greatly appreciated.

John Baker
[email protected]
 
M

[MVP] S. Clark

The columnheadings property is used to force the order in which columns are
displayed. With a little VBA code, you can populate this value prior to
executing the query.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
J

John Baker

Thanks for your reply Steve.

Do you have an example of the sort of code you are suggesting?

How can you populate the columnheadings property prior to
executing the query.

I am also not sure how to have code run while executing a query.

John Baker
[email protected]
 
D

Duane Hookom

I recommend using a relative date as the column heading. For instance, if
you want 10 columns with the most recent date being Forms!frmA!txtEndDate
then use a column heading expression like:
ColHead: "Day" & DateDiff("d",[DateField],Forms!frmA!txtEndDate)
Then add this to your Query|Parameters
Forms!frmA!txtEndDate Date/Time
and set your Column Headings property to:
"Day0","Day1","Day2",..."Day9"
The Day0 column is generated for the date entered on the form. Day9 is 9
days previous. You can then create a report of form based on these
consistently named columns/fields.
 
Top