By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,731 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

Sorting Where Sort Field Is Null

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a

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

P: n/a
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

P: n/a
"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.