Bill:
I was able to export a crosstab with 69 columns and 397 rows without error.
I believe 255 columns is the maximum for an Access query. Whatever issue
you are having, it appears specific to your implementation.
Regarding the second question, there should be no reason why using a string
variable for the file name should cause an issue. Again, I tested this and
did not receive an error.
If you can give me more specifics regarding your code, I will try to help
you pinpoint the issue.
--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com
This response is supplied "as is" without any representations or warranties.
The query appears fine.
It is the result of a crosstab query. The number of columns and rows
varies. I know it works because I have an option to print to a report using
the same query. Printing (using a report), and running the query in access
works fine, export doesn't when there's a "higher" column count. For
example, if I run the query using data that creates 7 columns and 12 rows,
that works. Change the parameters so that the query produces 20 columns and
10 rows, and the export fails.
This is what the crosstab looks like:
TRANSFORM Sum(Qry_EmpHrs_Total_By_Project_Task.SumOfEmpHrs) AS
SumOfSumOfEmpHrs
SELECT Qry_EmpHrs_Total_By_Project_Task.Task,
Sum(Qry_EmpHrs_Total_By_Project_Task.SumOfEmpHrs) AS TotTaskHrs
FROM Qry_EmpHrs_Total_By_Project_Task
WHERE (((Qry_EmpHrs_Total_By_Project_Task.Project_Label) = 'CSMES'))
GROUP BY Qry_EmpHrs_Total_By_Project_Task.Task
PIVOT Qry_EmpHrs_Total_By_Project_Task.Initials;
The exact error msg is:
Run-time error '3190:'
Too many fields defined.
Also, another question. I want to use a string variable for the filename in
the transferspreadsheet command, but I get an error when I try.
dim fName as string
fName= "long file name"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,
"Qry_EmpHrs_Total", fName, True
How can I get that to work?