Another quick one!
Trying to search for records by both accounting period and by year in two seperate queries actioned by option buttons. I've sorted the formatting so it's in a UK format but I keep getting the same error
'(3122) you tried to execute a query that does not include the specific expression 'tblAccounting.Period=0406 And tblProduction.DepartmentID=1 ?And tblUtility.UtilityID=1' as part of an aggregate function.'
I can't see what the problem is here and it's really starting to get me down. I've tried everything that I can think. The code below is what I'm using. Any help wopuld be greatly appreciated.
JP ; )
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, AVG(tblProduction.ProductionVolume) As 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) "
'GROUP BY
ECSQL = ECSQL & "GROUP BY tblAccounting.Date, tblProduction.ProductionVolume, tblBudget.Budget, tblProduction.DepartmentID, tblUtility.UtilityID "
'OPTION BUTTON SELECTION
Select Case Frame40
Case 1
'LAST 7 DAYS
ECSQL = ECSQL & "HAVING (tblAccounting.Date )< #" & Format(Date - 7, "dd/mm/yyyy") & "#"
Case 2
'PERIOD TO DATE
ECSQL = ECSQL & "HAVING tblAccounting.period = " & DLookup("period", "tblAccounting", "date=#" & Format(Date, "dd/mmm/yyyy") & "#")
Case 3
'YEAR TO DATE
ECSQL = ECSQL & "HAVING right(tblAccounting.period,2) = " & Right(DLookup("period", "tblAccounting", "date=#" & Format(Date, "dd/mm/yyyy") & "#"), 2)
Case 4
'DATE RANGE
ECSQL = ECSQL & "HAVING tblAccounting.Date BETWEEN #" & txtStart & "# AND #" & txtEnd & "#"
End Select
ECSQL = ECSQL & " AND ((tblProduction.DepartmentID)=1) AND ((tblUtility.UtilityID)=1)"
'ECRecordSet.Open ECSQL
CurrentDb.Execute ECSQL
mysheetpath = "g:\Utilities\Database(2)\Database\Department\char ts.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tblResults", mysheetpath, True, "output"
x = Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe g:\Utilities\Database(2)\Database\Department\chart s.xls", vbMaximizedFocus)
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Set Xl = CreateObject("Excel.application")
Set XlBook = GetObject(mysheetpath)
Xl.Visible = True
XlBook.Windows(1).Visible = True
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
Excel.Application.Quit
End Sub