Sorting Where Sort Field Is Null

[Marie]

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

[Duane Hookom]

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

[pietlinden]

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...)

[Douglas J. Steele]

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

[Bob Quintal]

"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.