Sorting Where Sort Field Is Null

  • Thread starter Thread starter Marie
  • Start date Start date
M

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
 
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.
 
Marie said:
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
Click to expand...

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...)
 
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.
 
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.
Click to expand...

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