runtime error 3129, invalid SQL statement

[LB79]

Hello,

I'm having a problem with the following SQL statement in VBA and wondered if anyone can see what I got wrong.
The SQL was taken from a crosstab query I created but when I run it, I get runtime error 3129, invalid SQL statement. Expected DELETE, INSERT, PROCEDURE, SELECT, UPDATE.

Thanks for any help

[SIZE=3][COLOR=#000080][FONT=Arial]mySQL = ""[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]mySQL = mySQL & "TRANSFORM Sum(C20_tblTarget.[Target Amount]) AS [SumOfTarget Amount]"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]mySQL = mySQL & "SELECT C20_tblTargetDetails.[Agent ID], C20_tblTarget.[Item ID], C20_tblTargetDetails.Category, C20_tblTargetDetails.FY, C20_tblTargetDetails.[Start Month], C20_tblTargetDetails.Port, C20_tblTargetDetails.Currency, C20_tblTargetDetails.Vendor, C20_tblTargetDetails.Budgeted, C20_tblTargetDetails.Description"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]mySQL = mySQL & "FROM C20_tblTarget INNER JOIN C20_tblTargetDetails ON C20_tblTarget.[Item ID] = C20_tblTargetDetails.[Item ID]"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]mySQL = mySQL & "WHERE (((C20_tblTargetDetails.FY) = '08'))"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]mySQL = mySQL & "GROUP BY C20_tblTargetDetails.[Agent ID], C20_tblTarget.[Item ID], C20_tblTargetDetails.Category, C20_tblTargetDetails.FY, C20_tblTargetDetails.[Start Month], C20_tblTargetDetails.Port, C20_tblTargetDetails.Currency, C20_tblTargetDetails.Vendor, C20_tblTargetDetails.Budgeted, C20_tblTargetDetails.Description"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]mySQL = mySQL & "ORDER BY C20_tblTarget.[Item ID]"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]mySQL = mySQL & "PIVOT C20_tblTarget.[Saving Month];"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]DoCmd.RunSQL "mySQL"[/FONT][/COLOR][/SIZE]

 

[pbaldy]

For starters, RunSQL can only be used for action queries, which crosstab is not. Second, you need to add spacing between lines. As it is, the lines will be jammed together with no spaces:

TRANSFORM Sum(C20_tblTarget.[Target Amount]) AS [SumOfTarget Amount]SELECT C20...

I don't see anything dynamic in that SQL, so why not just use a saved query?

 

[LB79]

Thanks for the advice. The SQL will be dynamic... This is my starting point to make sure I can get it working before I move on.

 

[LB79]

Just one more quetsion... if I cant use RunSQL for crosstab, how can I run the SQL?

Thanks

 

[pbaldy]

You could try DoCmd.OpenQuery. I never let users see anything but forms and reports, so I'd be using that SQL as the source of a report.

 

[LB79]

So Id just have
DoCmd.OpenQuery "MySQL"
?
I didnt realise that could be done... Ill give it a go - Thanks

 

[pbaldy]

Actually now that I think about it, I think OpenQuery requires a saved query. You'd have to use a QueryDef to set the SQL of a saved query to your dynamic SQL.