SQL

[Benno2805]

I have created the following query in Access and pasted the SQL into VBA to run in Excel.
I've done this many times before, but this time it's a crosstab query and it doesn't like the TRANSFORM...any ideas?


ComplaintsSQL = "TRANSFORM Count(dbo_tbl_complaint_type.description) AS CountOfdescription" & _
"SELECT dbo_tbl_complaint_type.description" & _
"FROM (dbo_tbl_contact_reason INNER JOIN dbo_tbl_customer ON dbo_tbl_contact_reason.contact_reason_id = dbo_tbl_customer.contact_reason_id) INNER JOIN dbo_tbl_complaint_type ON dbo_tbl_customer.complaint_type_id = dbo_tbl_complaint_type.complaint_type_id" & _
"WHERE (((dbo_tbl_customer.date_created) >= #8/1/2003# And (dbo_tbl_customer.date_created) <= #10/31/2003#))" & _
"GROUP BY dbo_tbl_complaint_type.description" & _
"ORDER BY dbo_tbl_complaint_type.description, Format([dbo_tbl_customer].[date_created],'mm')" & _
"PIVOT Format([dbo_tbl_customer].[date_created],'mm');"




thanks in advance.

 

[amigos]

I had the same problem once and it looks like with TRANSFORM syntax you have to make sure you have row breaks (chr(10)) after every SQL command. So try:

"SELECT dbo_tbl_complaint_type.description" & Chr(10) & _
"FROM ................................................... & Chr(10) & _
etc

HTH

 

[Benno2805]

Works like a dream...Thanks.