groupe Header sorting problem

[Lazc]

Hi,
i have a report that contains a groupe header. this header contains the months of the year. problem is, when you create a new group header, you need to specify a sort order (ascending or descending). but months are not sorted in alphabetical order. furthermore, i have to display the months of one fiscal year (fiscal year goes from april to march of the next year). I have a table containing the months along with an id (month id) telling in what order months have to show up. but i cant sort the group header of my reports using this field (the month id).
even if my query sorts the data using the monthid, the groupheader is still sorted in alphabetical order.

how can i specify the sort order of a group ???

 

[dhookom]

You stated "table containing the months along with an id" but didn't provide some sample data, field names, or even the data type of the fields. Access does an excellent job of sorting by what you tell it to sort by.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Lazc]

well i didnt provide some because of the description i gave but things always seem obvious to someone who knows what he's talking about. Sorry about that

the first two columns are

month_id (primary key) from 1 to 12 (integer)
month_desc from april to march (text)

month_id month_desc
1 april
2 may
... ...
12 march

i have no problem sorting the data, the problem lies in the report, when you create a group header (view : sorting and grouping) the only two options you have for sorting are ascending and descending. i cant choose anything else

 

[dhookom]

Is there a reason why you can't sort on the Month_ID fields since it seems to fit your requirements?

What is stored in your actual table? Do you have a real date field that describes your expected order? How is the table you described related to your report's record source?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Lazc]

as i said, i have no problem sorting the data itself. it's only when the report is displayed that the months are not sorted correctly. i want to know if theres a way to sort the order of appearance of GROUP HEADERs in a report, because obviously, if it was simple data, it would have been sorted in the same order as in the table of query

i am not displaying anything else for the moment. only the months of the year in that special order. nothing else is stored in the table, except for the monts (in a different language but that has nothing to do with it)

the grouping and sorting option does not give me the possibility to sort using another field. it can only sort data by alphabetical order.

 

[dhookom]

You should never rely on a report to be sorted the same as the report's record source query. This is rarely if ever reliable.

The Sorting and Grouping dialog will have a field/expression dropdown that includes all of the sortable fields from your report's record source. This will exclude memo and a couple other field types but should include all numeric and text fields.

In your case, I would expect the Month_ID field to display for selection. Then set the order to Ascending.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Lazc]

alright thanks

 

[Lazc]

i actually finally understood what my problem was... after i displayed the monthid, i understood that the sorting wasnot made correctly : instead of doing 1,2,3,4...12. it would do 1,11,12,2,3,4...

this field is seen as if it was a text field eventhoug in the table it is an integer. so i had to replace the monthid by letters a, b, c, ...

does anyone know how i can make sure that if my header group uses an int, it will sort it like an int instead of text...?

 

[dhookom]

You can set the sorting expression to
=Val([YourTextNumberField])

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Lazc]

it was a good idea but it does not change anything

 

[Lazc]

ok i got it
i had to make group on interval ! dont ask me why, i have no idea !