Why won't my code work (1 Viewer)

[geoffcodd]

I have the following code but it just won't work

DoCmd.RunSQL "INSERT INTO CBUDPresG ( Utility_Type, Month, Period, SumOfCost" & _
"SELECT [Central_Budget_Utilities_Data_(Present)].Utility_Type, [Central_Budget_Utilities_Data_(Present)].Period AS Month, Format(DateAdd("d",7,[Date_TaxPoint]),"yyyymm") AS Period, Sum([Central_Budget_Utilities_Data_(Present)].Cost) AS SumOfCost" & _
"FROM [Central_Budget_Utilities_Data_(Present)]" & _
"GROUP BY [Central_Budget_Utilities_Data_(Present)].Utility_Type, [Central_Budget_Utilities_Data_(Present)].Period, Format(DateAdd("d",7,[Date_TaxPoint]),"yyyymm")" & _
"ORDER BY [Central_Budget_Utilities_Data_(Present)].Utility_Type, Format(DateAdd("d",7,[Date_TaxPoint]),"yyyymm");"

Any help appreciated
Thanks
Geoff

 

[Tim K.]

You typed in the code or you cut and pasted?

You'll need space at the end of each SQL line and I prefer Execute method to the one you use.

Dim dbs As Database
Set dbs = CurrentDb

dbs.Execute "INSERT INTO CBUDPresG ( Utility_Type, Month, Period, SumOfCost " & _ 
"SELECT [Central_Budget_Utilities_Data_(Present)].Utility_Type, [Central_Budget_Utilities_Data_(Present)].Period AS Month, " & _
"Format(DateAdd("d",7,[Date_TaxPoint]),"yyyymm") AS Period, Sum([Central_Budget_Utilities_Data_(Present)].Cost) AS SumOfCost " & _ 
"FROM [Central_Budget_Utilities_Data_(Present)] " & _ 
"GROUP BY [Central_Budget_Utilities_Data_(Present)].Utility_Type, " & _
"[Central_Budget_Utilities_Data_(Present)].Period, Format(DateAdd("d",7,[Date_TaxPoint]),"yyyymm") " & _ 
"ORDER BY [Central_Budget_Utilities_Data_(Present)].Utility_Type, Format(DateAdd("d",7,[Date_TaxPoint]),"yyyymm");"

dbs.Close
set dbs = Nothing

 

[geoffcodd]

Thanks Tim,

I cut and pasted the sql, now I get a syntax error

Any Ideas

Thanks
Geoff

 

[Autoeng]

There is a known bug with the way that Access handles Access SQL queries. Did you change the SQL after pasting it? If so you can't do that. Instead paste the SQL into a Notepad document, make your changes there, then paste into the query.

Autoeng