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 should use the built-in Format function to convert a normal date into an alphabetic string for cross-tabulation purposes. This might be the day of week or the month of year—in the example shown in Figure 1-9, we took the date field, DateAcquired, and formatted it as a three-letter month string. Add the remaining fields to qryAlbumTypeByMonth2, as shown in Table 1-3.

   Table 1-3. Field settings for the qryAlbumTypeByMonth2 crosstab query

   Field	Table	Total	Crosstab
   AlbumType	tblAlbums	Group By	Row Heading
   Month: Format([DateAcquired], “mmm”)		Group By	Column Heading
   Album ID	tblAlbums	Count	Value

   All crosstab queries must have at least three fields: Row Heading, Column Heading, and Value.

3. Select View → Properties if the properties sheet is not already visible. Click on any part of the background of the upper-half of the query screen. This will select the properties for the query itself (as opposed to the Field or FieldList properties). Enter the values of the formatted date, in the order in which you want them to appear, into the ColumnHeadings property. For the qryAlbumTypeByMonth2 query, add three-letter strings for each month of the year (see Figure 1-8). Separate each entry with a comma.

   

   Figure 1-8. The query properties sheet for qryAlbumByMonth2

4. Save and run the query. The date columns should be ordered chronologically.

Now run qryAlbumTypeByMonth2, which you’ll also find in 01-03.MDB. In this query, the months are ordered chronologically (see Figure 1-9).

Figure 1-9. The months in qryAlbumTypeByMonth2 sort chronologically

Discussion

When you convert a date/time field to a formatted date using the Format function, Access converts the date into a string. This means that the formatted date will sort alphabetically, like any other string. Access includes a special query property, ColumnHeadings, to make it easy to work around this unpleasant side effect of using the Format function.

You aren’t limited to using fixed column headings with formatted date strings. This crosstab query property comes in handy for several other situations. For example, you might use the ColumnHeadings property to:

• Force a crosstab to always contain a column heading, even if no values exist for that column. For example, you could use the ColumnHeadings property to include all employee names in a crosstab report, even if one of the employees has no sales for the reporting period.

• Force a unique ordering for the columns of a crosstab query. For example, if your Column Heading field is made up of the names of regions, you can use the ColumnHeadings property to ensure that the home region always appears as the leftmost column.

• Eliminate a column value. If the ColumnHeadings property contains any values, any column headings in the result set that are not listed in the property will be left out of the crosstab query. Of course, you can also accomplish this by using query criteria.

Warning

When you use the ColumnHeadings property, you must spell the column heading values exactly as they appear in your data. If you misspell a column heading value, that value will not appear in the crosstab query. For example, if you use Format(datefield, "ddd") as the Column Heading field and create fixed column headings of Mon, Tue, Wed, Thr, Fri, Sat, and Sun, the Thr column will be completely blank because the Format function returns Thu (not Thr) for day of week.

You cannot set the ColumnHeadings property programmatically. Setting this property in the Access user interface causes an IN clause to be added to the SQL of the query, and the only way to accomplish this in code is to add or modify that IN clause yourself.