Recipe 1.4 Make Formatted Date Columns Sort Correctly in a Crosstab Query
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-04.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.
Figure 1-7. The months in qryAlbumTypeByMonth1 sort alphabetically
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?
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
Create a select query. Select Query Crosstab to convert
the query into a crosstab query.
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-7, 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.
All crosstab queries must have at least three fields: Row Heading,
Column Heading, and Value.
Table 1-3. Field settings for the qryAlbumTypeByMonth2 crosstab query
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
Save and run the query. The date columns should be ordered
Now run qryAlbumTypeByMonth2, which you'll also find
in 01-04.MDB. In this query, the months are
ordered chronologically (see Figure 1-9).
Figure 1-9. The months in qryAlbumTypeByMonth2 sort chronologically
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
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
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. You can also
accomplish this by using query criteria.
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
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.