How to sort months on report 2

[ribbons]

Ok, I know this is probably a dumb question, but this is something I've never run across before.

I have a query that sorts data by date, from July of one year through June of the next. In the design of the query, I have selected to sort the date Ascending. If I run the query, I get the months in calendar order, i.e. July, Aug, Sept, etc.

And, I have a report that gets its data from this query. When the data is displayed on the report, the data is sorted and displayed by inspector and month. The months are sorts ascending, again, but when I preview the report, the months are sorted alphabetically. Is it just a bug in Access? As I said, of all the reports I've done in this fashion, I've never run across this before. Any help would be appreciated.

ribbons

 

[ClydeData]

With the report in design view use the menu item
View>Sorting And Grouping

Hope this helps

Jimmy

 

[ribbons]

Hi ClydeData,

Yep, I know about that. But the two options are "Ascending" or "Descending" and either of them alphabetizes the months. I suppose I have just never had an issue with this, but I would very much like for these months to display in calendar order, but I couldn't do it where you suggest.

ribbons

 

[TheAceMan1]

How are ya ribbons . . .

It sounds as if your month field is text (Jan, Feb, . . .). If this is true add the following custom field to the query and sort on that instead.

[blue]idxMonth:Switch(Left([Month], 3) = "Jan", 1, Left([Month], 3) = "Feb", 2, Left([Month], 3) = "Mar", 3, Left([Month], 3) = "Apr", 4, Left([Month], 3) = "May", 5, Left([Month], 3) = "Jun", 6, Left([Month], 3) = "Jul", 7, Left([Month], 3) = "Aug", 8, Left([Month], 3) = "Sep", 9, Left([Month], 3) = "Oct", 10, Left([Month], 3) = "Nov", 11, Left([Month], 3) = "Dec", 12)[/blye]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[TheAceMan1]

. . . so sorry. Should be:

[blue]idxMonth:Switch(Left([Month], 3) = "Jan", 1, Left([Month], 3) = "Feb", 2, Left([Month], 3) = "Mar", 3, Left([Month], 3) = "Apr", 4, Left([Month], 3) = "May", 5, Left([Month], 3) = "Jun", 6, Left([Month], 3) = "Jul", 7, Left([Month], 3) = "Aug", 8, Left([Month], 3) = "Sep", 9, Left([Month], 3) = "Oct", 10, Left([Month], 3) = "Nov", 11, Left([Month], 3) = "Dec", 12)[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[ribbons]

Of course. Geez, I'd overlooked the fact the field was text. Duh.

I tried this and it works . . . except it sorts it as:

1
10
11
12

etc., etc. I tried putting "001" and so forth, but it would let me. Ideas?

 

[SgtJarrow]

In the query, add Month([Datefield]) and then sort on that.

The Month function returns a INTEGER of the month, not a text comparison. The sort against this should be correct.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[ribbons]

That did it! Thanks so much to you both!

ribbons