sortin a report by NULL valuse

[dshemesh]

Hello,
I have a report which I want to sort by the date field of a query which is
the source of the report. Some of the records in the query have a NULL date
field. The problem is that I want the dates to appear from latest to
earliest, but I want the records in the query which have a NULL date field,
to appear first.
How can I do this?
Thank you

 

[Rick Brandt]

Hello,
I have a report which I want to sort by the date field of a query which is
the source of the report. Some of the records in the query have a NULL date
field. The problem is that I want the dates to appear from latest to
earliest, but I want the records in the query which have a NULL date field,
to appear first.
How can I do this?
Thank you

Isn't that what happens by default? Access/Jet usually sorts Nulls to the top
whereas some other databases would sort them to the bottom. What results are
you getting now?

 

[dshemesh]

Now I'm getting first all the records with dates, from latest to earliest,
and only then I get the fields in which the date is null (The sorting isn't
in the query itself, it is done in the report).
If I sort from earliest to latest, Iget the null-date records first.

 

[Rick Brandt]

Now I'm getting first all the records with dates, from latest to earliest,
and only then I get the fields in which the date is null (The sorting isn't
in the query itself, it is done in the report).
If I sort from earliest to latest, Iget the null-date records first.

My mistake, I missed the part about getting values WITH dates in descending
order.

Simplest is to open the Sorting and Grouping dialog and make your top level sort
on the expression...

Nz([YourDateField], #12/31/3000#)

....which will treat Nulls as the date you enter in the expression and bring them
to the top when you sort in descending order.

 

[dshemesh]

I tried doing it, but the report won't accept it.
When I try openning the report I get a box which asks me to insert a value
for
Nz(closingDate, #31/12/3000#)

Now I'm getting first all the records with dates, from latest to earliest,
and only then I get the fields in which the date is null (The sorting isn't
in the query itself, it is done in the report).
If I sort from earliest to latest, Iget the null-date records first.

My mistake, I missed the part about getting values WITH dates in descending
order.

Simplest is to open the Sorting and Grouping dialog and make your top level sort
on the expression...

Nz([YourDateField], #12/31/3000#)

....which will treat Nulls as the date you enter in the expression and bring them
to the top when you sort in descending order.

 

[Rick Brandt]

Try putting "=" in front of it.

=Nz(closingDate, #31/12/3000#)

I tried doing it, but the report won't accept it.
When I try openning the report I get a box which asks me to insert a value
for
Nz(closingDate, #31/12/3000#)

Now I'm getting first all the records with dates, from latest to earliest,
and only then I get the fields in which the date is null (The sorting isn't
in the query itself, it is done in the report).
If I sort from earliest to latest, Iget the null-date records first.

My mistake, I missed the part about getting values WITH dates in descending
order.

Simplest is to open the Sorting and Grouping dialog and make your top level
sort
on the expression...

Nz([YourDateField], #12/31/3000#)

....which will treat Nulls as the date you enter in the expression and bring
them
to the top when you sort in descending order.

 

[dshemesh]

great! Thanks!

Try putting "=" in front of it.

=Nz(closingDate, #31/12/3000#)

I tried doing it, but the report won't accept it.
When I try openning the report I get a box which asks me to insert a value
for
Nz(closingDate, #31/12/3000#)

Now I'm getting first all the records with dates, from latest to earliest,
and only then I get the fields in which the date is null (The sorting isn't
in the query itself, it is done in the report).
If I sort from earliest to latest, Iget the null-date records first.

My mistake, I missed the part about getting values WITH dates in descending
order.

Simplest is to open the Sorting and Grouping dialog and make your top level
sort
on the expression...

Nz([YourDateField], #12/31/3000#)

....which will treat Nulls as the date you enter in the expression and bring
them
to the top when you sort in descending order.