John,
Thanks for your quick response. I added the columns at
the end of the query grid -- should I have inserted them
next to the 2004, 2005, and 2006 fields, or in place of
them? As it is, I still get the same result (shortened
sample):
2004 2005 2006
AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank) and so forth
EEE 01/01/06
FFF 03/01/06
GGG 07/01/06
HHH 12/01/06
III 01/01/05
JJJ 03/01/05
KKK 07/01/05
LLL 12/01/05
MMM 01/01/04 01/01/06
NNN 03/01/04
OOO 07/01/04 07/01/06
PPP 12/01/04 12/01/06
What I'm looking for is this:
MMM 01/01/04 01/01/06
NNN 03/01/04
OOO 07/01/04 07/01/06
PPP 12/01/04 12/01/06
III 01/01/05
JJJ 03/01/05
KKK 07/01/05
LLL 12/01/05
EEE 01/01/06
FFF 03/01/06
GGG 07/01/06
HHH 12/01/06
AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
If it helps, this is an audit schedule. Some business
areas (AAA, etc.) will be audited once in the three-year
period, others twice, others not at all (thus the blank
fields). Can I get the blanks at the bottom of each column
without the dates being sorted backward (Dec-Jan)? For
those with both 2004 and 2006 dates, I only need the 2004
dates in order, and obviously, the business areas are not
involved in the sort at all.
Thank you!!
-----Original Message-----
One easy method is to add additional columns to sort by. You don't need to show
the columns, but you can sort by them.
ORDER BY NZ([2004],#1/1/9999#), NZ([2005],#1/1/9999#),NZ ([2006],#1/1/9999#)
In the query grid you would add three columns as above
Field: NZ([2004],#1/1/9999#)
Show: Not Checked
Sort: Ascending
I need to sort three columns (2004, 2005, and 2006) that
have both dates and blank fields. The desired result is
to have all the 2004 dates (Jan-Dec), then all the 2005
dates (Jan-Dec), etc. with all of each column's blank
fields at the bottom instead of at the top.
Help?
Thanks!
.
