CrossTab Query Month as ColumnHeader but sort by Name

[mrchonginhk]

I have a Crosstab query with date as column field.
I have formatted this date field so that it is Jan, Feb etc.
However, the output is such that the column sorting is based on the spelling of these Jan, Feb so order is Dec, Feb, Mar, Jan, Jul etc instead of Jan, Feb, Mar....
What is the solution to make the columns Jan, Feb, Mar etc ?
Thanks

 

[SydneyGeek]

In Design view, right-click in the grid of the month field and select Properties.
In the Column Headings property put the months in their correct order, eg
'Jan','Feb','Mar' ... 'Dec'

Denis

 

[mrchonginhk]

Thanks. I searched the article showing exact solution like yours.
But I have a problem with this approach. That will always show 12 columns and I do not want blank columns to appear. ie if there is only May and Jun data, that solution will have all other ten columns of zero values which is not I want... help...

Is it possible the pivot field be sorted based on other value like MonthValue but header text use month name ?

 

[SydneyGeek]

I'm not sure that you can, at least not through the interface.

If the month is pivoted to the Row Heading you can sort by month number and put other items across the top but otherwise I'm not sure how you would do it without VBA.

Denis