|
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.
| 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
tbl_Whatever
And it returns
ALL
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
UNION
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.
|
|
