Sorting dates

  • Thread starter Thread starter Jaycee
  • Start date Start date
J

Jaycee

I've created a query on which to base a report showing a
three-year 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)? Also, how do I
eliminate the records that contain no date? For records
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.

Following are the current results:

2004 2005 2006

AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06

What I'm looking for is this:

MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06

....with these records eliminated:
AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
Any help would be greatly appreciated!
 
To remove the all-blank records, add this to the WHERE clause or your query:
WHERE NOT ([2004] Is Null AND [2005] Is Null AND [2005] Is Null)

To sort the null records to the bottom, add calculated fields like this:
Blank2004: ([2004] Is Null)
and sort Descending on these fields before (i.e. left of) the other sorting
requirements.
 
Not sure exactly what you want. You could perhaps combine the values of the
YesNo output in some way?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Jaycee said:
Thanks, Allen! Your suggestions accomplished everything
I needed with one exception: the 2005 dates aren't
picking up where 2004 leaves off but are at the bottom
instead. Below are the results of the query (headings
are misaligned due to window restrictions). Do I have the
Blank fields in the wrong place?

Blank2004 Date2004 Blank2005 Date2005 Blank2006
Date2006
0 1/1/2004 -1 -1
0 2/1/2004 -1 0 8/1/2006
0 2/4/2004 -1 0 4/1/2006
0 3/1/2004 -1 -1
0 3/1/2004 -1 -1
0 4/1/2004 -1 -1
0 4/1/2004 -1 -1
0 6/1/2004 -1 -1
0 6/1/2004 -1 -1
0 6/1/2004 -1 0 6/1/2006
0 6/1/2004 -1 0 7/1/2006
0 7/1/2004 -1 -1
0 7/1/2004 -1 0 9/1/2006
0 8/1/2004 -1 -1
0 8/1/2004 -1 -1
0 9/1/2004 -1 -1
0 9/1/2004 -1 0 7/1/2006
0 10/1/2004 -1 -1
0 10/1/2004 -1 0 10/1/2006
0 11/1/2004 -1 -1
0 11/1/2004 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 0 1/1/2006
-1 -1 0 2/1/2006
-1 -1 0 3/1/2006
-1 -1 0 3/1/2006
-1 -1 0 4/1/2006
-1 -1 0 4/1/2006
-1 -1 0 5/1/2006
-1 -1 0 6/1/2006
-1 -1 0 6/1/2006
-1 -1 0 8/1/2006
-1 -1 0 8/1/2006
-1 -1 0 9/1/2006
-1 -1 0 11/1/2006
-1 -1 0 11/1/2006
-1 -1 0 11/1/2006
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 2/1/2005 -1
-1 0 3/1/2005 -1
-1 0 3/1/2005 -1
-1 0 3/1/2005 -1
-1 0 4/1/2005 -1
-1 0 5/1/2005 -1
-1 0 5/1/2005 -1
-1 0 6/1/2005 -1
-1 0 6/1/2005 -1
-1 0 6/1/2005 -1
-1 0 7/1/2005 -1
-1 0 8/1/2005 -1
-1 0 8/1/2005 -1
-1 0 9/1/2005 -1
-1 0 10/1/2005 -1
-1 0 10/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 12/1/2005 -1


-----Original Message-----
To remove the all-blank records, add this to the WHERE clause or your query:
WHERE NOT ([2004] Is Null AND [2005] Is Null AND [2005] Is Null)

To sort the null records to the bottom, add calculated fields like this:
Blank2004: ([2004] Is Null)
and sort Descending on these fields before (i.e. left of) the other sorting
requirements.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Jaycee said:
I've created a query on which to base a report showing a
three-year 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)? Also, how do I
eliminate the records that contain no date? For records
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.

Following are the current results:

2004 2005 2006

AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06

What I'm looking for is this:

MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06

...with these records eliminated:
AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
Any help would be greatly appreciated!
Click to expand...


.
Click to expand...
Click to expand...
 
It's perfect as is, except that I'd like the blanks in
the 2005 column to be at the bottom (as they are for
2004). I'm puzzled as to why the Blank200X: ([Date200X]
Is Null) works for 2004 but not for 2005.

I appreciate your time -- I'm also interested in checking
out your web site.
-----Original Message-----
Not sure exactly what you want. You could perhaps combine the values of the
YesNo output in some way?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Jaycee said:
Thanks, Allen! Your suggestions accomplished everything
I needed with one exception: the 2005 dates aren't
picking up where 2004 leaves off but are at the bottom
instead. Below are the results of the query (headings
are misaligned due to window restrictions). Do I have the
Blank fields in the wrong place?

Blank2004 Date2004 Blank2005 Date2005 Blank2006
Date2006
0 1/1/2004 -1 -1
0 2/1/2004 -1 0 8/1/2006
0 2/4/2004 -1 0 4/1/2006
0 3/1/2004 -1 -1
0 3/1/2004 -1 -1
0 4/1/2004 -1 -1
0 4/1/2004 -1 -1
0 6/1/2004 -1 -1
0 6/1/2004 -1 -1
0 6/1/2004 -1 0 6/1/2006
0 6/1/2004 -1 0 7/1/2006
0 7/1/2004 -1 -1
0 7/1/2004 -1 0 9/1/2006
0 8/1/2004 -1 -1
0 8/1/2004 -1 -1
0 9/1/2004 -1 -1
0 9/1/2004 -1 0 7/1/2006
0 10/1/2004 -1 -1
0 10/1/2004 -1 0 10/1/2006
0 11/1/2004 -1 -1
0 11/1/2004 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 0 1/1/2006
-1 -1 0 2/1/2006
-1 -1 0 3/1/2006
-1 -1 0 3/1/2006
-1 -1 0 4/1/2006
-1 -1 0 4/1/2006
-1 -1 0 5/1/2006
-1 -1 0 6/1/2006
-1 -1 0 6/1/2006
-1 -1 0 8/1/2006
-1 -1 0 8/1/2006
-1 -1 0 9/1/2006
-1 -1 0 11/1/2006
-1 -1 0 11/1/2006
-1 -1 0 11/1/2006
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 2/1/2005 -1
-1 0 3/1/2005 -1
-1 0 3/1/2005 -1
-1 0 3/1/2005 -1
-1 0 4/1/2005 -1
-1 0 5/1/2005 -1
-1 0 5/1/2005 -1
-1 0 6/1/2005 -1
-1 0 6/1/2005 -1
-1 0 6/1/2005 -1
-1 0 7/1/2005 -1
-1 0 8/1/2005 -1
-1 0 8/1/2005 -1
-1 0 9/1/2005 -1
-1 0 10/1/2005 -1
-1 0 10/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 12/1/2005 -1


-----Original Message-----
To remove the all-blank records, add this to the WHERE clause or your query:
WHERE NOT ([2004] Is Null AND [2005] Is Null AND
Click to expand...
[2005]
Is Null)
To sort the null records to the bottom, add calculated fields like this:
Blank2004: ([2004] Is Null)
and sort Descending on these fields before (i.e. left of) the other sorting
requirements.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

I've created a query on which to base a report
Click to expand...
showing
a
three-year 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)? Also, how do I
eliminate the records that contain no date? For records
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.

Following are the current results:

2004 2005 2006

AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06

What I'm looking for is this:

MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06

...with these records eliminated:
AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
Any help would be greatly appreciated!




.
Click to expand...
Click to expand...


.
Click to expand...
 
PERHAPS

SELECT ListOfFieldDesired
FROM TableName
WHERE NOT ([2004] Is Null AND [2005] Is Null AND [2006] Is Null)
ORDER BY IsNull([2004]) Desc, [2004],
IsNull([2005]) Desc, [2005],
IsNull([2006]) Desc, [2006]

It's perfect as is, except that I'd like the blanks in
the 2005 column to be at the bottom (as they are for
2004). I'm puzzled as to why the Blank200X: ([Date200X]
Is Null) works for 2004 but not for 2005.

I appreciate your time -- I'm also interested in checking
out your web site.
-----Original Message-----
Not sure exactly what you want. You could perhaps combine the values of the
YesNo output in some way?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Jaycee said:
Thanks, Allen! Your suggestions accomplished everything
I needed with one exception: the 2005 dates aren't
picking up where 2004 leaves off but are at the bottom
instead. Below are the results of the query (headings
are misaligned due to window restrictions). Do I have the
Blank fields in the wrong place?

Blank2004 Date2004 Blank2005 Date2005 Blank2006
Date2006
0 1/1/2004 -1 -1
0 2/1/2004 -1 0 8/1/2006
0 2/4/2004 -1 0 4/1/2006
0 3/1/2004 -1 -1
0 3/1/2004 -1 -1
0 4/1/2004 -1 -1
0 4/1/2004 -1 -1
0 6/1/2004 -1 -1
0 6/1/2004 -1 -1
0 6/1/2004 -1 0 6/1/2006
0 6/1/2004 -1 0 7/1/2006
0 7/1/2004 -1 -1
0 7/1/2004 -1 0 9/1/2006
0 8/1/2004 -1 -1
0 8/1/2004 -1 -1
0 9/1/2004 -1 -1
0 9/1/2004 -1 0 7/1/2006
0 10/1/2004 -1 -1
0 10/1/2004 -1 0 10/1/2006
0 11/1/2004 -1 -1
0 11/1/2004 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 0 1/1/2006
-1 -1 0 2/1/2006
-1 -1 0 3/1/2006
-1 -1 0 3/1/2006
-1 -1 0 4/1/2006
-1 -1 0 4/1/2006
-1 -1 0 5/1/2006
-1 -1 0 6/1/2006
-1 -1 0 6/1/2006
-1 -1 0 8/1/2006
-1 -1 0 8/1/2006
-1 -1 0 9/1/2006
-1 -1 0 11/1/2006
-1 -1 0 11/1/2006
-1 -1 0 11/1/2006
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 2/1/2005 -1
-1 0 3/1/2005 -1
-1 0 3/1/2005 -1
-1 0 3/1/2005 -1
-1 0 4/1/2005 -1
-1 0 5/1/2005 -1
-1 0 5/1/2005 -1
-1 0 6/1/2005 -1
-1 0 6/1/2005 -1
-1 0 6/1/2005 -1
-1 0 7/1/2005 -1
-1 0 8/1/2005 -1
-1 0 8/1/2005 -1
-1 0 9/1/2005 -1
-1 0 10/1/2005 -1
-1 0 10/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 12/1/2005 -1



-----Original Message-----
To remove the all-blank records, add this to the WHERE
clause or your query:
WHERE NOT ([2004] Is Null AND [2005] Is Null AND [2005]
Is Null)

To sort the null records to the bottom, add calculated
fields like this:
Blank2004: ([2004] Is Null)
and sort Descending on these fields before (i.e. left
of) the other sorting
requirements.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

I've created a query on which to base a report showing
a
three-year 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)? Also, how
do I
eliminate the records that contain no date? For records
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.

Following are the current results:

2004 2005 2006

AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06

What I'm looking for is this:

MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06

...with these records eliminated:
AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
Any help would be greatly appreciated!




.
Click to expand...


.
Click to expand...
Click to expand...
 
Perfect! Thank you!!

-----Original Message-----
PERHAPS

SELECT ListOfFieldDesired
FROM TableName
WHERE NOT ([2004] Is Null AND [2005] Is Null AND [2006] Is Null)
ORDER BY IsNull([2004]) Desc, [2004],
IsNull([2005]) Desc, [2005],
IsNull([2006]) Desc, [2006]

It's perfect as is, except that I'd like the blanks in
the 2005 column to be at the bottom (as they are for
2004). I'm puzzled as to why the Blank200X: ([Date200X]
Is Null) works for 2004 but not for 2005.

I appreciate your time -- I'm also interested in checking
out your web site.
-----Original Message-----
Not sure exactly what you want. You could perhaps combine the values of the
YesNo output in some way?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Thanks, Allen! Your suggestions accomplished everything
I needed with one exception: the 2005 dates aren't
picking up where 2004 leaves off but are at the bottom
instead. Below are the results of the query (headings
are misaligned due to window restrictions). Do I
Click to expand...
have
the
Blank fields in the wrong place?

Blank2004 Date2004 Blank2005 Date2005 Blank2006
Date2006
0 1/1/2004 -1 -1
0 2/1/2004 -1 0 8/1/2006
0 2/4/2004 -1 0 4/1/2006
0 3/1/2004 -1 -1
0 3/1/2004 -1 -1
0 4/1/2004 -1 -1
0 4/1/2004 -1 -1
0 6/1/2004 -1 -1
0 6/1/2004 -1 -1
0 6/1/2004 -1 0 6/1/2006
0 6/1/2004 -1 0 7/1/2006
0 7/1/2004 -1 -1
0 7/1/2004 -1 0 9/1/2006
0 8/1/2004 -1 -1
0 8/1/2004 -1 -1
0 9/1/2004 -1 -1
0 9/1/2004 -1 0 7/1/2006
0 10/1/2004 -1 -1
0 10/1/2004 -1 0 10/1/2006
0 11/1/2004 -1 -1
0 11/1/2004 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 0 1/1/2006
-1 -1 0 2/1/2006
-1 -1 0 3/1/2006
-1 -1 0 3/1/2006
-1 -1 0 4/1/2006
-1 -1 0 4/1/2006
-1 -1 0 5/1/2006
-1 -1 0 6/1/2006
-1 -1 0 6/1/2006
-1 -1 0 8/1/2006
-1 -1 0 8/1/2006
-1 -1 0 9/1/2006
-1 -1 0 11/1/2006
-1 -1 0 11/1/2006
-1 -1 0 11/1/2006
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 2/1/2005 -1
-1 0 3/1/2005 -1
-1 0 3/1/2005 -1
-1 0 3/1/2005 -1
-1 0 4/1/2005 -1
-1 0 5/1/2005 -1
-1 0 5/1/2005 -1
-1 0 6/1/2005 -1
-1 0 6/1/2005 -1
-1 0 6/1/2005 -1
-1 0 7/1/2005 -1
-1 0 8/1/2005 -1
-1 0 8/1/2005 -1
-1 0 9/1/2005 -1
-1 0 10/1/2005 -1
-1 0 10/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 12/1/2005 -1



-----Original Message-----
To remove the all-blank records, add this to the WHERE
clause or your query:
WHERE NOT ([2004] Is Null AND [2005] Is Null AND [2005]
Is Null)

To sort the null records to the bottom, add calculated
fields like this:
Blank2004: ([2004] Is Null)
and sort Descending on these fields before (i.e. left
of) the other sorting
requirements.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

I've created a query on which to base a report showing
a
three-year 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)? Also, how
do I
eliminate the records that contain no date? For records
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.

Following are the current results:

2004 2005 2006

AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06

What I'm looking for is this:

MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06

...with these records eliminated:
AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
Any help would be greatly appreciated!




.



.
Click to expand...
Click to expand...
.
Click to expand...
 
Reports have their own sorting. They don't follow the query sorting except by coincidence.

Include the IsNull(2004), etc as fields in the query and include them as fields
(hidden) on your report.

Now, open the Sorting and Grouping dialog in the report and set up the sorting
in the same order as in the query.
John,

Thank you for the solution to the query problem. I
assumed (incorrectly) that if the query worked, I'd be
able to produce a report based on it that would also have
the dates sorted correctly. But that wasn't the result --
the dates show up in the report with the blank fields at
the top of each column rather than at the bottom, which
was my original problem in the query. (In the report
wizard, I set the date columns to sort ascending.)

I tried using a portion of the query's code in the
report, but apparently that's not the solution or I'm
putting it in the wrong place.

I appreciate your help on this!

BTW, I'll also post this question in the Reports &
Printing forum.
-----Original Message-----
Perfect! Thank you!!

-----Original Message-----
PERHAPS

SELECT ListOfFieldDesired
FROM TableName
WHERE NOT ([2004] Is Null AND [2005] Is Null AND
Click to expand...
[2006]
Is Null)
ORDER BY IsNull([2004]) Desc, [2004],
IsNull([2005]) Desc, [2005],
IsNull([2006]) Desc, [2006]


Jaycee wrote:

It's perfect as is, except that I'd like the blanks in
the 2005 column to be at the bottom (as they are for
2004). I'm puzzled as to why the Blank200X: ([Date200X]
Is Null) works for 2004 but not for 2005.

I appreciate your time -- I'm also interested in checking
out your web site.

-----Original Message-----
Not sure exactly what you want. You could perhaps
combine the values of the
YesNo output in some way?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Thanks, Allen! Your suggestions accomplished
everything
I needed with one exception: the 2005 dates aren't
picking up where 2004 leaves off but are at the bottom
instead. Below are the results of the query (headings
are misaligned due to window restrictions). Do I have
the
Blank fields in the wrong place?

Blank2004 Date2004 Blank2005 Date2005 Blank2006
Date2006
0 1/1/2004 -1 -1
0 2/1/2004 -1 0 8/1/2006
0 2/4/2004 -1 0 4/1/2006
0 3/1/2004 -1 -1
0 3/1/2004 -1 -1
0 4/1/2004 -1 -1
0 4/1/2004 -1 -1
0 6/1/2004 -1 -1
0 6/1/2004 -1 -1
0 6/1/2004 -1 0 6/1/2006
0 6/1/2004 -1 0 7/1/2006
0 7/1/2004 -1 -1
0 7/1/2004 -1 0 9/1/2006
0 8/1/2004 -1 -1
0 8/1/2004 -1 -1
0 9/1/2004 -1 -1
0 9/1/2004 -1 0 7/1/2006
0 10/1/2004 -1 -1
0 10/1/2004 -1 0 10/1/2006
0 11/1/2004 -1 -1
0 11/1/2004 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 -1
-1 -1 0 1/1/2006
-1 -1 0 2/1/2006
-1 -1 0 3/1/2006
-1 -1 0 3/1/2006
-1 -1 0 4/1/2006
-1 -1 0 4/1/2006
-1 -1 0 5/1/2006
-1 -1 0 6/1/2006
-1 -1 0 6/1/2006
-1 -1 0 8/1/2006
-1 -1 0 8/1/2006
-1 -1 0 9/1/2006
-1 -1 0 11/1/2006
-1 -1 0 11/1/2006
-1 -1 0 11/1/2006
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 1/1/2005 -1
-1 0 2/1/2005 -1
-1 0 3/1/2005 -1
-1 0 3/1/2005 -1
-1 0 3/1/2005 -1
-1 0 4/1/2005 -1
-1 0 5/1/2005 -1
-1 0 5/1/2005 -1
-1 0 6/1/2005 -1
-1 0 6/1/2005 -1
-1 0 6/1/2005 -1
-1 0 7/1/2005 -1
-1 0 8/1/2005 -1
-1 0 8/1/2005 -1
-1 0 9/1/2005 -1
-1 0 10/1/2005 -1
-1 0 10/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 11/1/2005 -1
-1 0 12/1/2005 -1



-----Original Message-----
To remove the all-blank records, add this to the WHERE
clause or your query:
WHERE NOT ([2004] Is Null AND [2005] Is Null AND
[2005]
Is Null)

To sort the null records to the bottom, add calculated
fields like this:
Blank2004: ([2004] Is Null)
and sort Descending on these fields before (i.e. left
of) the other sorting
requirements.

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html

I've created a query on which to base a report
showing
a
three-year 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)?
Click to expand...
Also,
how
do I
eliminate the records that contain no date? For
records
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.

Following are the current results:

2004 2005 2006

AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06

What I'm looking for is this:

MMM 01/01/04 (blank) 01/01/06
NNN 03/01/04 (blank) (blank)
OOO 07/01/04 (blank) 07/01/06
PPP 12/01/04 (blank) 12/01/06
III (blank) 01/01/05 (blank)
JJJ (blank) 03/01/05 (blank)
KKK (blank) 07/01/05 (blank)
LLL (blank) 12/01/05 (blank)
EEE (blank) (blank) 01/01/06
FFF (blank) (blank) 03/01/06
GGG (blank) (blank) 07/01/06
HHH (blank) (blank) 12/01/06

...with these records eliminated:
AAA (blank) (blank) (blank)
BBB (blank) (blank) (blank)
CCC (blank) (blank) (blank)
DDD (blank) (blank) (blank)
Any help would be greatly appreciated!




.



.

.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top