Returning Crosstab query properties

[Lobster1071]

Anyone have an idea why I am unable to get certain properties of a Dynamic Crosstab query, when it works for other standard (select) queries? The Crosstab query works fine when displayed to the screen, I just can't get any properties (I am going to use the info to create a report).

I'm simply trying to get the field count, and field names of each column, yet this code basically displays nothing:

Set qd = CurrentDb().QueryDefs("Schedule")

Debug.Print qd.Fields.Count

For x = 0 To qd.Fields.Count - 1
Debug.Print qd.Fields(x).Name
Next x

The number of fields always comes up with 0, and the field names are blank/null. However, like I said, the query exists and is displayed to the screen with no problem.

Thanks for any information.

 

[JHB]

I would use a recordset instead!
Does the Crosstab query has any criteria?
The field names are blank/null, because you'll not get into the For-Next loop when the fields count is 0.

 

[Lobster1071]

Thanks for the reply.

Yes, the crosstab does have criteria/parameters from a form, a beginning and ending date that the user enters. I did add those 2 parameters to the query, and have the form open with info in those fields when I run the query. As I said, the query works great, and displays to the screen fine. I just can't get any properties from it which is strange.

I did however find another way to account for the dynamically changing column headings and create the report I needed. I will still look into doing it via the recordset way that you suggested. Always like to find better or more efficient ways of doing things. Just bothers me that I still can't return simple properties from this crosstab query.
Thanks again for your help.

 

[apr pillai]

Take a look at the code presented in the following link, dealing with the same issue.

Dynamic Report.

 

[CJ_London]

to use a crosstab as a recordsource to a form or report you must set the column headings in the query properties

in sql it would look something like

TRANSFORM..
...
...
PIVOT myTable.myColumns IN (1,2,3)