sorting crosstab query columns 1

[kobooky]

I have a crosstab querry.
The column headings are derived from data inserted by the user and therefore I can't predict what they will be.
This is why I can't use the 'column headings" property.
It is also important to keep the syntax of the headings exactly as the user entered it.

I have two ideas but I can't get any of them work:
First:
To concatenate the heading strings with a leading number that will do the sorting.
But in order to maintain the original string - to add a "Backspace" charecter in the string so that the visible string will be the same as the original.
Probleme: the backspace charecter didn't hide the leading number.
Does anyone know why?

Second:
To calculate the field of the column headings in the crosstab as a subquery.
The subquery shall be sorted in the correct order and maybe this will do the job.
probleme: The systax! no mater what I've tried it always gives me an error.
Can anyone help me with the correct syntax?

 

[dhookom]

I doubt either solution will work. Crosstabs don't get along with subqueries.

I would use code to build the entire sql of the crosstab including the Column Headings property. Use a little DAO code to modify the SQL like:

  Dim strSQL as String
  strSQL = "TRANSFORM...."
  'add lots more stuff
  'loop through something to create your "In ()"
  strSQL = strSQL & "IN (....)"
  Currentdb.QueryDefs("qxtbName").SQL = strSQL

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kobooky]

Thanks a lot dhookom!
A simple answer but very usfull.
I followed your advise and realy solved the probleme.

Thanks again!