cross tab query - column headings sorting

[kobooky]

I use a crosstab query.
The column headings is a field with user filled data that can be any string he wishes (there is no rule or limitation for this field and there shouldn't be)
Therefor I cannot tell what will be the actual names of the columns when the query is run.
The user has to fill in for each name a number for sorting.
I want the crosstab query to sort the columns according to this number and not according to the name.
How can this be done?
please help.

 

[r937]

concatenate the number to the front of the name

TRANSFORM ...
SELECT ...
FROM ...
GROUP BY ...
PIVOT CStr(num) & '-' & name

yes, the column headings will be n-aaaaa format, but that's better than nothing, eh?

r937.com | rudy.ca

 

[kobooky]

Thanks.
This will surely sort the column correctly but...
I use the column headings in a chart in a report and these names form the legend for the chart.
So in this way the legend come out wrong.
I'm looking for something else:
mayby a smart expression in the grid of the query or a subquery to define the order.
But I don't know how.
I was doing some reading in the forum and I found that others had the same probleme but they didn't get any answer that made it work.
I'm beginning to think that there is no way to do this.
Do you know how to address the "column headings" property of a query through VBA?

 

[kobooky]

I had an idea:
use concatenation and between the tow parts - inserting chr(8) (backspace).
I tried it and it didnt work.
no real backspace was done.

Can someone tell me why?