sortin a report by NULL valuse

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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

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

Rick Brandt said:
dshemesh said:
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.
Click to expand...

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.
Click to expand...
 
Try putting "=" in front of it.

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


dshemesh said:
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#)

Rick Brandt said:
dshemesh said:
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.
Click to expand...

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.
Click to expand...
Click to expand...
 
great! Thanks!

Rick Brandt said:
Try putting "=" in front of it.

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


dshemesh said:
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#)

Rick Brandt said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top