Runtime Error 3078: Unknown - SQL help required!

[JP]

Managed to get the SQL sorted for the issues I was having yesterday, now
though when I run the query I am faced with the error above everytime, the
code is as follows with all the optional extra bits included:

Private Sub lblEC_Click()

Dim x
Dim TABLESQL As String
Dim ECSQL As String

'ERROR HANDLING

On Error Resume Next
DoCmd.DeleteObject acTable, "tblResults"
On Error GoTo 0

'CREATE TABLE SQL

TABLESQL = "CREATE TABLE tblResults ([Date] DATETIME, [ProductionVolume]
LONG, [Usage] LONG"
If chkBudgetUsage Then TABLESQL = TABLESQL & ", [BudgetUsage] LONG"
If chkActualCost Then TABLESQL = TABLESQL & ", [ActualCost] CURRENCY "
If chkBudgetCost Then TABLESQL = TABLESQL & ", [BudgetCost] CURRENCY "
TABLESQL = TABLESQL & ")"
CurrentDb.Execute TABLESQL

'INSERT INTO SQL

ECSQL = "INSERT INTO tblResults ([Date], [ProductionVolume], [Usage]"
If chkBudgetUsage Then ECSQL = ECSQL & ", [BudgetUsage]"
If chkActualCost Then ECSQL = ECSQL & ", [ActualCost]"
If chkBudgetCost Then ECSQL = ECSQL & ", [BudgetCost]"
ECSQL = ECSQL & ") "

'SELECT INTO SQL

ECSQL = ECSQL & "SELECT DISTINCT tblAccounting.Date,
tblProduction.ProductionVolume, Sum(tblReading.ReadingVolume) AS [Usage] "
If chkBudgetUsage Then ECSQL = ECSQL & ", tblBudget.Budget AS BudgetUsage "
If chkActualCost Then ECSQL = ECSQL & ",
Sum(tblreading.readingvolume*tbltariff.tariff) AS ActualCost "
If chkBudgetCost Then ECSQL = ECSQL & ",
Avg(tblbudget.budget*tblTariff.Tariff) AS BudgetCost "


'FROM SQL

ECSQL = ECSQL & "FROM tblUtility INNER JOIN ((tblPeriod INNER JOIN
((tblAccounting INNER JOIN (tblProduction INNER JOIN tblBudget ON
tblProduction.DepartmentID = tblBudget.DepartmentID) ON (tblAccounting.Date =
tblProduction.Date) AND (tblAccounting.Date = tblBudget.Date)) INNER JOIN
(tblReading INNER JOIN tblMeter ON tblReading.MeterID = tblMeter.MeterID) ON
tblAccounting.Date = tblReading.Date) ON tblPeriod.Period =
tblAccounting.Period) INNER JOIN tblTariff ON tblPeriod.Period =
tblTariff.Period) ON (tblUtility.UtilityID = tblTariff.UtilityID) AND
(tblUtility.UtilityID = tblMeter.UtilityID) AND (tblUtility.UtilityID =
tblBudget.UtilityID) "


'OPTION BUTTON SELECTION

Select Case Frame40
Case 1
'LAST 7 DAYS
ECSQL = ECSQL & "GROUP BY tblAccounting.Date,
tblProduction.ProductionVolume, tblBudget.Budget, tblProduction.DepartmentID,
tblUtility.UtilityID "
ECSQL = ECSQL & "HAVING tblAccounting.Date > #" & Format(Date - 7,
"dd/mmm/yyyy") & "# "
ECSQL = ECSQL & "AND ((tblProduction.DepartmentID)=1) AND
((tblUtility.UtilityID)=1)"
Case 2
'PERIOD TO DATE
ECSQL = ECSQL & "WHERE (((tblProduction.DepartmentID) = 1) And
((tblUtility.UtilityID) = 1) And ((tblAccounting.Period) =
DLookup(tblAccounting.period, tblaccounting.period, tblaccounting.date=#" &
Date & "#))) "
ECSQL = ECSQL & "GROUP BY tblAccounting.Date,
tblProduction.ProductionVolume, tblBudget.Budget "
Case 3
'YEAR TO DATE
ECSQL = ECSQL & "HAVING right(tblAccounting.period,2) = " &
Right(DLookup("period", "tblAccounting", "date=#" & Format(Date,
"dd/mmm/yyyy") & "#"), 2)
Case 4
'DATE RANGE
ECSQL = ECSQL & "GROUP BY tblAccounting.Date,
tblProduction.ProductionVolume, tblBudget.Budget, tblProduction.DepartmentID,
tblUtility.UtilityID "
ECSQL = ECSQL & "HAVING tblAccounting.Date BETWEEN #" &
Format(txtStart, "dd/mmm/yyyy") & "# AND #" & Format(txtEnd, "dd/mmm/yyyy") &
"#"
ECSQL = ECSQL & " AND ((tblProduction.DepartmentID)=1) AND
((tblUtility.UtilityID)=1)"

End Select

CurrentDb.Execute ECSQL

mysheetpath = "g:\Utilities\Database(2)\Database\Department\charts.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tblResults",
mysheetpath, True, "xyz"

x = Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe
g:\Utilities\Database(2)\Database\Department\charts.xls", vbMaximizedFocus)

Excel.Application.Quit

End Sub

Any help would be greatly appreciated.