Report not showing right sort order!

[sjl]

My report is based on a crosstab query. The crosstab query shows the sort order I want, but the report does not!

These are the 2 columns (in the crosstab) that i'm using to accomplish my sorting. Is there a mistake?

Field:........Format ([date],"mm") ...........Month: Format([date],"mmm")
Total:.......Group By.............................Group By
Crosstab:..(not shown).........................Row Heading
Sort:.........Ascending...........................(not sorted)

So, I want it sorted by digit month (01, 02, 03...12), but I want it shown by the character month (Jan, Feb, Mar...)

 

[Pat Hartman]

The report uses its own sorting and grouping. That means that you need to include the field you want to sort on in the report's record source which it doesn't look like you are doing.

BTW, I never format dates in queries for just this reason. Do your formatting in the report and your sorting will always be correct for dates.

 

[sjl]

Thanks Pat!

I learned that there was sorting/grouping property in Report (right click on properties box of Report). This helped a lot.

I still seemingly need to format in the query because I need to get the counts per month, but to get it to display the way I want, I am doing a work-around using "mm-mmm" as the Month (e.g. 01-Jan, 02-Feb,) so that I get the right order plus the character month abbrev.

thanks again,
Sarah

 

[Brianwarnock]

If you just want to show the Month abbreviation you can, the fact that a field is in the source query makes it available for sorting in the report but it does not have to be in the report. So a field say Expr1:Format([datefield],"mm") can be sorted on but not selected for the report.

Brian

 

[sjl]

Brian,

Thanks!

I had gotten that to work in the crosstab query, but since I did not know about the sort/group property of the Report at that time, could not get it all working as I had wanted. Thanks for helping me put together the final piece of the puzzle

Sarah

 

[Pat Hartman]

In a crosstab query, use the column headings property to control the column order. This is safe for fixed lists like months or weekdays but be careful if your list might change. For example, if you KNOW that you will only work Monday-Friday and so those are the only column names you enter in the headings property, should someone work on Saturday or Sunday, your query would not select the record.