The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
May 2009

A Tricky Sorting Problem: Getting "All" to the Top of the List

As I've mentioned many times in the past, dealing with dates - whether it be calculation, sorting, or manipulation - is one of the toughest things that you may have to do with an Access application. I recently got an e-mail from a reader who has been struggling to sort a list of dates formatted as months, while allowing the user to select "all" as an option, which he wanted to place the top of list.

This month, we'll tackle this tricky problem of sorting dates as text and then putting it the word "All" at the top of the list.

In this Issue
  • Tip of the Month - Put Your Tricky Code into a Library
  • The Problem
  • Challenges With This Issue
  • The Solution: Inject a Sorting Column
  • Points to Note

  • The Problem

    I received the following e-mail from Access Wizard reader Brian B.

    Hi, Jim!

    Got any ideas for this one? I have records with a date field, and need to return a list of dates by month, with an "ALL" entry at the top.

    The trouble is, I can't seem to get Access to sort the list BY THE DATE VALUE of each month when I add the "ALL" using a union query. Here's what I have...

    SELECT FromMonth FROM (SELECT Format(fld_ValDate, 'mmm yyyy') AS FromMonth FROM tbl_Whatever WHERE fld_ValDate > #12/31/2005# GROUP BY Format(fld_ValDate, 'mmm yyyy')) AS FM

    ORDER BY DateValue (Format(FromMonth, 'm/d/yyyy')) UNION SELECT ' ALL' FROM

    And it returns
    Apr 2006
    Apr 2007
    Apr 2008
    Aug 2006

    Challenges With This Issue

    What Brian is trying to do is a reasonable thing. He wants to make life easy for his users; he wants to show them a set of months and allow them to choose either a single month or all months.

    He has correctly figured out how to get the word "all" to the top of list: By using a union query and then giving it a leading character that sorts it to the top. (If you need more information about union queries, see July 2005 Wizard: Union Queries - The Urge to Merge.)

    Because Brian wants to show the dates formatted as months and years, he has formatted the date using the format statement Format(fld_ValDate, 'mmm yyyy').

    However with the formatting statement, the date field is turned into a string, which is why he's getting a series of Aprils at the top of his list rather than the column ordered by the underlying date.

    Brian has solved two of the three problems. He has correctly gotten the "all" to the top of the column and he has properly formatted dates to show months and years. The only real part that's left is to sort properly.

    The Solution: Inject a Sorting Column

    If we take this SQL statement and add a column dedicated to doing nothing but sorting, we can solve the problem. We want to sort by the true underlying date and get "All Dates" to the top the list. The following will do the trick:

    SELECT Format([fldDate],"Mmm YYYY") AS DateToUse, tblDateDemo.fldDate as SortOnMe
    FROM tblDateDemo
    Select "All Dates" as DateToUse, #1/1/1900# as SortOnMe from tblDatedemo
    ORDER BY SortOnMe;

    The main difference is that we have added a new field after the date field called "SortOnMe". That field is using the data from the field fldDate. Following that, it adds the union statement repeating the initial field name, but using the "All Dates" value, followed by #1/1/1900# as the SortOnMe Field.

    Notice that the order by statement uses this field SortOnMe. With this approach, we get the following in output.

    Notice that we have the rows in the order we want, as well as the "All Dates" as our very first entry. Also notice that the date I used for the All Dates is January 1, 1900. I chose this because I knew it would be earlier than any other date that might be used.

    Points to Note

    At this point, the main problems have been solved. If we were using this in a drop-down or combo box, we could certainly have formatted dates to achieve a similar result. If you were to use the extra column in the query, you would hide that column from the user.

    This is not the only way that this problem could have been solved. It's just one approach that works.

    Tip of the Month - Put Your Tricky Code into a Library

    This month, we broke out a number of different tools to solve a tough problem. What increases the ability to solve something like this is the availability of bits and pieces of your code or tricks that you've learned.

    Record solutions to things you've learned along the way and put them into a database so, when you have a similar problem in the future, you won't have to reinvent the wheel.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    Safe Unsubscribe
    This email was sent to jim@custom-software.biz by jim@custom-software.biz.

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886