date not sorting properly in report

[jh3016]

I have a report that has a field entitled "date". I want
this field to sort by month. However, when I sort by
month, the dates are not in order. As a matter of fact, I
can't tell what order they are in. This is really
stumping me. What am I missing? HELP!

 

[scriverb]

First of all look at the field in the table and make sure that the data type is identified as Date/Time and not Text. If it is text then that is why it will not sort properly.

Let me know what you find.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jh3016]

Thanks for the quick reply. I did check and it is formatted as Date/Time.

I tried using the sort from the report, and that didn't work. Then I tried using the sort from the underlying query and that seems to work. However, I really wanted it to keep all the same month together. With the sort from the underlying query, it will not group the month together, it will just sort correctly.

Any idea as to how to group by month?

 

[dhookom]

The sort of the underlying query has little or no effect on the order of the report. You must use the report sorting and grouping dialog. To sort by month you have to decide if you also want by year. A easy method is to set the field/expression to:
=Format([DateField],"yyyymm&quot
This will sort by year and month. For only month, remove the "yyyy".

 

[Norris68]

As dhookom says, the sort order of the underlying query will be overwritten by any grouping/sorting fields on the report. Also as dhookom says, you will want a field that specifies your year/month - I would generate it in your underlying query. Then, do a sort on the year/month followed by the date. Finally, try to avoid using the reserved word 'date' for your field names - you could either try enclosing it in square brackets within your report, or better still aliasing it in your underlying query (e.g. in query builder use "MyDate: [Date]" as the field name, or in raw SQL "[Date] AS MyDate" in your select list)

 

[isorders]

I have tried a couple of suggestions on this subject with no luck.
I would like the records to be displayed as...
June
6/01/2003-Task 1
6/05/2003-Task 2
6/22/2003 Task 3
etc.
Instead I am getting...
June
6/05/2003 Task 2
6/01/2003 Task 1
6/22/2003 Task 3
How do I sort by the month and have the items in the detail sort in Ascending order?

 

[scriverb]

You have to designate two fields as being sorted. If you are using the Sorting and Grouping in the report just select a secondary sort. Go to the next row down from the Month sort and select the Task number field. Now you will sort by the month and then task number with each month.

Post back if you have more questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jh3016]

Thanks a bunch, I will try this and post back the results.

 

[isorders]

Worked for me Bob...Thanks!!!!
I setup two Sorts on the same field
Date- Group On Month, results in Date Header
Date- Group On Individual Value, results in detail date text box