Sorting Crosstab query

[tkaplan]

I have a crosstab query where my column headings are the names of the months based on invoice dates.
what i currently have:
Month: MonthName(Month([AnnualIncome]![InvoiceDate]))
so this spits out April May and June in the column headings.
The problem is that it is sorting it alphabetically, not by the numerical value - so my column headings are coming out as april, june, then may. (I assume once I have july that will come in between april and june.)

Any help would be REALLY appreciated.

Thanks in advance.

 

[Pat Hartman]

Use the ColumnHeadings property of the ColumnHeading. Enter the values in the order you want them displayed:

"Jan","Feb","Mar", etc.

 

[tkaplan]

thank you for the reply, however, I cannot do that because more columns are added as more values are added.

My structure:

I have a table called "AnnualIncome" with fields:
Region
State
CenterID
InvoiceDate
InvoiceAmount
PaymentAmount

crosstab query "YTDIncome"
Region....GroupBy.....RowHeading
State.....GroupBy.....RowHeading
CenterID..GroupBy....RowHeading
Month.....GroupBy....ColumnHeading
PaymentAmount....Sum....Value

Month is MonthName(Month([AnnualIncome]![InvoiceDate]))

As the year progresses, I get more months to add to this query but i want them to stay in the chronological order of the calendar, not alphabetical order of the name of the months.

Thank you.

 

[Pat Hartman]

Having the 12 month names in the ColumnHeadings property will make the number of columns fixed rather than variable but it is the only to modify the order of the columns if they cannot be ordered on their actual names.

You should be using reports for display purposes in any event. Users should never be exposed to queries or table datasheets. When creating a crosstab report, it is far easier if the columns are fixed. You can create the report, bind it to the crosstab with fixed columns and in the report's open event hide the future month headers.

 

[tkaplan]

this is being exported to an excel file.... it has to stay that way b/c not all of the people who access this report use access (and to teach them would be a whole nother problem)

if i set them as fixed, how do i assign the values?