1.4 Make Formatted Date Columns Sort Correctly in a Crosstab Query

1.4 Make Formatted Date Columns Sort Correctly in a
Crosstab Query

1.4.1 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

1.4.2 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梚n 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

1.4.3 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.

 

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.