Crosstab query date field sorted by day instead of month

[Reva]

I have created a crosstab query with the date field as a column heading which
is to sort the date in ascending order from left to right. It is sorting the
date by the day instead of by the month, then day. e.g. the following dates
were sorted in this order - 05-Apr-06, 09-Jan-06, 18-May-06, 21-Mar-06
instead of 09-Jan-06, 21-Mar-06, 05-Apr-06, 18-May-06.

Please help as this is also affecting the report.

 

[John Spencer]

Since the dates are formatted then they will be shown in text sort order.
IF you want them in date order then they cannot be formatted.

In the crosstab query you can specify the field name(s) using an In clause
in the PIVOT statement.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT MonthFieldNames In ("09-Jan-06", "21-Mar-06", "05-Apr-06",
"18-May-06")

In the query grid, you do this by
-- Select View properties
-- Click on the grey area above the grid, so you are looking at the
query's properties
-- Inputting your values in Column Headings separated by commas (or
semicolons if your separator is semi-colons)

When you do this the specified cross-tab columns will show up and ONLY those
crosstab columns will be visible. If you mistype a value, you will get a
column with that name and no data (all nulls) in that column.

By the way, you do realize that a report based on the crosstab will fail if
the columns that are returned change.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..