Sorting Dates in Reports

[HaleDog]

I am new to Access so I apologize - I am sure this is easy to do but I can't figure it out!

On my form I have a date field set in a the following format - xx/xx/xxxx. I created a query based on this form that summarizes the date by month and just displays month and year (e.g., April 2003).

I have created a report to display the totals for each month and then the year to date totals for each person. The only problem that I am having is that I cannot get the date to sort correctly - it is sorting in alphabetical order - not by date. What do I need to do to get this to sort correctly?

Thank you in adavance.

Jennifer

 

[dcx693]

In your query, I'd just put a column with the actual dates in it (not formatted in any way) and sort on that field. That way, the now unsorted formatted dates will be sorted correctly. Just don't display the results by unchecking the "Show" button.

 

[Hayley Baxter]

You can go into design view of the report, right click on the report properties and Sorting and grouping. Select your date field and choose ascending or descending depending on whether you want to show the most recent date first or the oldest date. The other alternative is to do as dcx693 suggested by first sorting them in the query. Both ways will work.

 

[HaleDog]

I have set the sort on the date field on the Report but it is sorting alphabetically - not by date (e.g., April, February, January, March). I setup my query using a wizard and it shows a summary for each person for each month - if I sort on the date field in my query it also sorts alphabetically not by date.

I went back to my table to make sure the field is set as a date field and it is - it appears that in the query and report the information is not being recognized as a date.

Thank you for your responses . . . any other help would be appreciated.

Jennifer

 

[dcx693]

How did you format the field to look like April 2003? Are you sorting on that field?

 

[HaleDog]

I used the query wizard when I created the query and I used the summary option to sumarize the dates by month. In design view this is what the properties are for the date field:

Field: Date By Month : Format$(TrainingDate,'mmmm yyyy')
Table: Blank
Total: Group By
Sort: Ascending

 

[dcx693]

Insert a row into your query. Put this equation into the line:
NumericDate:Format([TrainingDate],"mm/yyyy")

On the sort line, choose Ascending (though to get it sorted in the report you don't really need to do this). In the total line, choose Group By. Remove the sort choice from the Date By Month field that you create using the query wizard.

This will create a field in your query, called NumericDate that when sorted, will present the months in the proper order.

In the report design, choose Sorting and Grouping and choose the NumericDate field you just created.

 

[Pat Hartman]

DO NOT format the date at all. Just add the unformatted date field to the query. If you format it as dcx693 suggests it will sort in month order. However if you have multiple years in the recordset, the results will not be as you expected. The data will be sorted as 01/02, 01/03, 02/02, 02/03, .....

When you use format to specify how a date will be displayed, the result is a string value rather than an numeric value. Therefore the only way to get a string date to sort into logical order, is to put it in year/month/day format.

Once you have the unformatted date in the query, you can add it to the report's sorting and grouping properties. Sorting the query does nothing except force the data to be sorted twice. The report ALWAYS sorts its recordset itself.

 

[dcx693]

Pat is right. You need to include a column in your query with the actual date. Sort and group on that actual date field in your report, but continue to display the formatted date in the report. The dates will all sort in the proper order.