getting number of columns from an SQL statement

[datenis]

How do you get the column count(?) from an sql query that's in the form of a crosstab query embedded in VBA?

thanks,

D1

 

[tomschafer]

If you have the query declared as a QueryDef you can use...

Dim q As QueryDef
q.Fields.Count

Please post the code if otherwise.

 

[MichaelRed]

That won't work, as it returns the number of cols in the query DESIGN. For Crosstab query, the number of columns in the design does not often equal the number of columns in the resultset.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[braindead2]

the pivot statement determines the column grouping so I would think if you build a query based on the grouping you could get a count

something like this

select count (*) as cnt from
[SELECT distinct format(sickdate,"mmm&quot
FROM tbl where sickdate >10/3/2001]. as a

for this crosstab query

TRANSFORM Count(tbl.recid) AS [The Value]
SELECT tbl.name
FROM tbl
WHERE (((tbl.sickdate)>#10/3/01#))
GROUP BY tbl.name
PIVOT Format(sickdate,"mmm"

good luck

 

[MichaelRed]

Braindead2's response (if the syntax is corrected) will give the # Cols in the PIVIOT part. You need to add 2 more for the value and sum cols.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over