469,292 Members | 1,438 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,292 developers. It's quick & easy.

Sorting Where Sort Field Is Null

My report has a text field named ItemNum. Most records have a value for
ItemNum. I set Grouping And Sorting to sort ascending on the ItemNum field.
The records where ItemNum is Null appear at the top of the list in the
report. I want the records that have a value for ItemNum to be first in the
report and sorted ascending and I want the records where ItemNum is Null to
be at the end of the report. How do I do this?

Thanks for all help!

Marie
Nov 13 '05 #1
4 8764
Set the sorting and grouping field/expression to something like:
=Nz([ItemNum], "zzzzzzzz")
or
=Nz([ItemNum],999999999)
This depends on your ItemNum field type and values.

--
Duane Hookom
MS Access MVP
--

"Marie" <pl****@no.spam> wrote in message
news:fJ*****************@newsread1.news.pas.earthl ink.net...
My report has a text field named ItemNum. Most records have a value for
ItemNum. I set Grouping And Sorting to sort ascending on the ItemNum
field.
The records where ItemNum is Null appear at the top of the list in the
report. I want the records that have a value for ItemNum to be first in
the
report and sorted ascending and I want the records where ItemNum is Null
to
be at the end of the report. How do I do this?

Thanks for all help!

Marie

Nov 13 '05 #2

Marie wrote:
My report has a text field named ItemNum. Most records have a value for ItemNum. I set Grouping And Sorting to sort ascending on the ItemNum field. The records where ItemNum is Null appear at the top of the list in the report. I want the records that have a value for ItemNum to be first in the report and sorted ascending and I want the records where ItemNum is Null to be at the end of the report. How do I do this?

Thanks for all help!

Marie


create a calculated field that forces the order into the one you want,
and don't show it? IIf(IsNull(MyField), "XXXX",[MyField]) and then
sort on that. You can't override the sort order any other way that I
can think of. (Means there should be at least ten ways...)

Nov 13 '05 #3
You can add a computed field that you use for sorting.

One approach is simply to have the computed field return 0 if the field's
not null, and 1 if it is. Something like:

SortField: IIf(IsNull([ItemNum]), 1, 0)

You'd then sort on SortField first, then ItemNum second.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Marie" <pl****@no.spam> wrote in message
news:fJ*****************@newsread1.news.pas.earthl ink.net...
My report has a text field named ItemNum. Most records have a value for
ItemNum. I set Grouping And Sorting to sort ascending on the ItemNum
field.
The records where ItemNum is Null appear at the top of the list in the
report. I want the records that have a value for ItemNum to be first in
the
report and sorted ascending and I want the records where ItemNum is Null
to
be at the end of the report. How do I do this?

Thanks for all help!

Marie

Nov 13 '05 #4
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
news:OZ**************@TK2MSFTNGP12.phx.gbl:
You can add a computed field that you use for sorting.

One approach is simply to have the computed field return 0 if
the field's not null, and 1 if it is. Something like:

SortField: IIf(IsNull([ItemNum]), 1, 0)

You'd then sort on SortField first, then ItemNum second.


Simpler still: sortfield: Isnull([itemnum]) descending.

--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by jwedel_stolo | last post: by
12 posts views Thread by pmud | last post: by
7 posts views Thread by Pete Davis | last post: by
7 posts views Thread by Kamal | last post: by
8 posts views Thread by sara | last post: by
7 posts views Thread by abracadabra | last post: by
5 posts views Thread by jrod11 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.