crosstab questions

M

Michael S. Montoya

Ok...I have setup a crosstab query. That part was fairly easy using the
wizard. Not the hard part is how to output the data!!!

A report's controls need to be bound to fields. However, with the crosstab,
the field names change when the groupings (column headings) change. How do
you tackle this?
 
D

Duane Hookom

If you want to push the query to Excel then you shouldn't need field names.
However:
Dim db as DAO.Database
Dim rs As DAO.Recordset
Dim fd As DAO.Field
Set db = CurrentDB
Set rs = db.OpenRecordset("qxtbYourQuery")
For Each fd in rs.Fields
debug.print fd.Name
Next
rs.Close
Set rs = Nothing
Set db = Nothing
 
J

Jamie Collins

Michael S. Montoya said:
I believe my final result would be best in Excel
What is the VBA code to loop through the field names of a query? Could this
be a method?
Click to expand...

Probably the easiest to way to create a new Excel table is to use the
SELECT..INTO syntax e.g. if you query looked like this:

SELECT MyTextCol
FROM MyTable
;

then add an INTO clause like this:

SELECT MyTextCol
INTO [Excel 8.0;HDR=YES;Database=C:\MyWorkbook.xls;].MyNewExcelTable
FROM MyTable
;

And if you just wanted the column headers, add a WHERE clause like
this:

SELECT MyTextCol
INTO [Excel 8.0;HDR=YES;Database=C:\MyWorkbook].MyExcelTable
FROM MyTable
WHERE 0=1
;

For an existing Excel table, use the INSERT INTO..SELECT syntax.

Jamie.

--
 
Top