Sorting by date

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

Guest

OK. I've spent way too much time already trying to solve something that
should be so simple. In a report, I want to sort by date ascending, but list
null fields at the bottom of the list.

I also had problems with the date sort in general. I want to display only
mm/yy in the report. I followed someone's suggestion and added an expression
to the query:

Expr1: Format([DateField,"mm/yy")

which I have set to "sort ascending" in the query.

I've replaced the [DateField] control in the report with [Expr1] from the
query, and inserted the following expression in the sort dialog box:

=Right([Expr1],2) & Left([Expr1],2)

The date sorting now works correctly (with the exception of the null values,
of course).

How do I "add" another expression to this, to define null values as a later
date? In general, I don't know how to include two different expressions.
How is this done?

Should I use something such as:

=IIf([DateField] Is Null,12/31/3000,[DateField])

or

=Nz([DateField], #12/31/3000#)

or should I be referring to the [Expr1] field?

And if so, where would I put these expressions???

Now that I have the [DateField] and the [Expr1], I've totally confused
things, as if it wasn't bad enough already.

It always amazes me how much time I waste trying to solve simple things like
this.

Thank you for any advice!
Diane
 
Set the Sorting and Grouping Field/Expression to:

=Nz([DateField],#12/31/3000#)

You should then be ok until the year 3001.
 
Create a query (if you don't already have one.)

In a fresh column in the field row enter:
NullDate: ([DateField] Is Null)
This field will return -1 (true) if the field is null, and 0 (false) if not.

Now, in report design view, open the Sorting And Grouping dialog (View
menu).
On the first row, choose the NullDate field, and choose Descending sort.
On the next row, choose the DateField, with Ascending sort.

The non-blank dates now sort first (that's the primary sort), and within
that, the records sort by the date.
 
Allen --- Works great. Thanks!

Allen Browne said:
Create a query (if you don't already have one.)

In a fresh column in the field row enter:
NullDate: ([DateField] Is Null)
This field will return -1 (true) if the field is null, and 0 (false) if not.

Now, in report design view, open the Sorting And Grouping dialog (View
menu).
On the first row, choose the NullDate field, and choose Descending sort.
On the next row, choose the DateField, with Ascending sort.

The non-blank dates now sort first (that's the primary sort), and within
that, the records sort by the date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

DiHo said:
OK. I've spent way too much time already trying to solve something that
should be so simple. In a report, I want to sort by date ascending, but
list
null fields at the bottom of the list.

I also had problems with the date sort in general. I want to display only
mm/yy in the report. I followed someone's suggestion and added an
expression
to the query:

Expr1: Format([DateField,"mm/yy")

which I have set to "sort ascending" in the query.

I've replaced the [DateField] control in the report with [Expr1] from the
query, and inserted the following expression in the sort dialog box:

=Right([Expr1],2) & Left([Expr1],2)

The date sorting now works correctly (with the exception of the null
values,
of course).

How do I "add" another expression to this, to define null values as a
later
date? In general, I don't know how to include two different expressions.
How is this done?

Should I use something such as:

=IIf([DateField] Is Null,12/31/3000,[DateField])

or

=Nz([DateField], #12/31/3000#)

or should I be referring to the [Expr1] field?

And if so, where would I put these expressions???

Now that I have the [DateField] and the [Expr1], I've totally confused
things, as if it wasn't bad enough already.

It always amazes me how much time I waste trying to solve simple things
like
this.

Thank you for any advice!
Diane
Click to expand...
Click to expand...
 
Back
Top