Grouping/Sorting by Date

I have a report which is a collection of dates. I want them grouped and
ordered by date so I setup the grouping and sort order accordingly. However,
it is not working?! It is not respecting the Ascending sort order on the
date field. Would anyone know a reason why this might happen and the
solution.

QB

On Wed, 10 Dec 2008 09:26:01 -0800, Question Boy wrote:

I have a report which is a collection of dates. I want them grouped and
ordered by date so I setup the grouping and sort order accordingly. However,
it is not working?! It is not respecting the Ascending sort order on the
date field. Would anyone know a reason why this might happen and the
solution.

QB

You haven't given enough good information (such as how is the actual
sort displaying), so my first guess would be that the dates are not
really date datatype values but more likely text values.
Also, you don't mention where you grouped and sorted the data.? The
only place to reliably sort data in a report is via the report's
sorting and grouping dialog, not in a query.... in Report Design View
click on View + Sorting and Grouping.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

I found a solution, don't know what the underlying promblem is, but should
this be able to help anyone else...

I create 3 fields in my underlying report query
eYear: Year([eDate])
eMonth: Month([eDate])
eDay: Day([eDate])

and then I changed my report grouping and sorting to use these 3 fields.

Seems to work.

"Question Boy" wrote:

I have a report which is a collection of dates. I want them grouped and
ordered by date so I setup the grouping and sort order accordingly. However,
it is not working?! It is not respecting the Ascending sort order on the
date field. Would anyone know a reason why this might happen and the
solution.

QB

Fredg

I checked, each date field is set to Date/Time type and I did indeed use the
report's sorting and grouping dialog to set the group and sort order. That
is why I don't understand why it is not working by default.

Actually, now that you make me think of it, the dates are a product of a
DMax function. Could this be the issue?

Still perplexed!!! (but at least I have a workaround for right now)

QB


"fredg" wrote:

On Wed, 10 Dec 2008 09:26:01 -0800, Question Boy wrote:

I have a report which is a collection of dates. I want them grouped and
ordered by date so I setup the grouping and sort order accordingly. However,
it is not working?! It is not respecting the Ascending sort order on the
date field. Would anyone know a reason why this might happen and the
solution.

QB

You haven't given enough good information (such as how is the actual
sort displaying), so my first guess would be that the dates are not
really date datatype values but more likely text values.
Also, you don't mention where you grouped and sorted the data.? The
only place to reliably sort data in a report is via the report's
sorting and grouping dialog, not in a query.... in Report Design View
click on View + Sorting and Grouping.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Is there a chance that the DMAX( ) could be returning a NULL value?

Have you tried forcing it back into a date, using the cDate( ) conversion
function?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"Question Boy" wrote:

Fredg

I checked, each date field is set to Date/Time type and I did indeed use the
report's sorting and grouping dialog to set the group and sort order. That
is why I don't understand why it is not working by default.

Actually, now that you make me think of it, the dates are a product of a
DMax function. Could this be the issue?

Still perplexed!!! (but at least I have a workaround for right now)

QB


"fredg" wrote:

On Wed, 10 Dec 2008 09:26:01 -0800, Question Boy wrote:

I have a report which is a collection of dates. I want them grouped and
ordered by date so I setup the grouping and sort order accordingly. However,
it is not working?! It is not respecting the Ascending sort order on the
date field. Would anyone know a reason why this might happen and the
solution.

QB

You haven't given enough good information (such as how is the actual
sort displaying), so my first guess would be that the dates are not
really date datatype values but more likely text values.
Also, you don't mention where you grouped and sorted the data.? The
only place to reliably sort data in a report is via the report's
sorting and grouping dialog, not in a query.... in Report Design View
click on View + Sorting and Grouping.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail