Access Cookbook

Make Formatted Date Columns Sort Correctly in a Crosstab Query

Problem

If you have a crosstab query that uses the built-in Format function to convert dates into text for column headings, Access sorts them alphabetically (Apr, Aug, and so on) rather than chronologically. For example, open 01-03.MDB and run the qryAlbumTypeByMonth1 crosstab query (see Figure 1-7). This query shows the cross-tabulation of the number of albums purchased by album type and the month the albums were purchased. The month columns are sorted alphabetically instead of chronologically.

When the purpose of using the month in a crosstab query is to examine chronological variation by month, this makes the crosstab query all but useless. Is there some way to tell Access to sort the columns by date rather than alphabetically?

Figure 1-7. The months in qryAlbumTypeByMonth1 sort alphabetically

Solution

The query properties sheet allows you to specify fixed column headings for a crosstab query. This solution illustrates how to use the ColumnHeadings property to specify column headings so that formatted dates sort chronologically.

Follow these steps to create a crosstab query with correctly sorted formatted-date columns:

1. Create a select query. Select Query → Crosstab to convert the query into a crosstab query.

2. Add the columns you want to the crosstab query. Use a calculation for the Column Heading field. This calculation ...