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.D epartmentID=1 ?And tblUtility.Util ityID=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.DeleteObj ect acTable, "tblResults "
On Error GoTo 0
'CREATE TABLE SQL
TABLESQL = "CREATE TABLE tblResults ([Date] DATETIME, [ProductionVolum e] 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.Execu te TABLESQL
'INSERT INTO SQL
ECSQL = "INSERT INTO tblResults ([Date], [ProductionVolum e], [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.D ate, AVG(tblProducti on.ProductionVo lume) As ProductionVolum e, Sum(tblReading. ReadingVolume) AS [Usage] "
If chkBudgetUsage Then ECSQL = ECSQL & ", tblBudget.Budge t 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.D epartmentID = tblBudget.Depar tmentID) ON (tblAccounting. Date = tblProduction.D ate) AND (tblAccounting. Date = tblBudget.Date) ) INNER JOIN (tblReading INNER JOIN tblMeter ON tblReading.Mete rID = tblMeter.MeterI D) ON tblAccounting.D ate = tblReading.Date ) ON tblPeriod.Perio d = tblAccounting.P eriod) INNER JOIN tblTariff ON tblPeriod.Perio d = tblTariff.Perio d) ON (tblUtility.Uti lityID = tblTariff.Utili tyID) AND (tblUtility.Uti lityID = tblMeter.Utilit yID) AND (tblUtility.Uti lityID = tblBudget.Utili tyID) "
'GROUP BY
ECSQL = ECSQL & "GROUP BY tblAccounting.D ate, tblProduction.P roductionVolume , tblBudget.Budge t, tblProduction.D epartmentID, tblUtility.Util ityID "
'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.p eriod = " & DLookup("period ", "tblAccounting" , "date=#" & Format(Date, "dd/mmm/yyyy") & "#")
Case 3
'YEAR TO DATE
ECSQL = ECSQL & "HAVING right(tblAccoun ting.period,2) = " & Right(DLookup(" period", "tblAccounting" , "date=#" & Format(Date, "dd/mm/yyyy") & "#"), 2)
Case 4
'DATE RANGE
ECSQL = ECSQL & "HAVING tblAccounting.D ate BETWEEN #" & txtStart & "# AND #" & txtEnd & "#"
End Select
ECSQL = ECSQL & " AND ((tblProduction .DepartmentID)= 1) AND ((tblUtility.Ut ilityID)=1)"
'ECRecordSet.Op en ECSQL
CurrentDb.Execu te ECSQL
mysheetpath = "g:\Utilities\D atabase(2)\Data base\Department \charts.xls"
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel8, "tblResults ", mysheetpath, True, "output"
x = Shell("C:\Progr am Files\Microsoft Office\OFFICE11 \excel.exe g:\Utilities\Da tabase(2)\Datab ase\Department\ charts.xls", vbMaximizedFocu s)
Dim Xl As Excel.Applicati on
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Set Xl = CreateObject("E xcel.applicatio n")
Set XlBook = GetObject(myshe etpath)
Xl.Visible = True
XlBook.Windows( 1).Visible = True
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
Excel.Applicati on.Quit
End Sub
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.D epartmentID=1 ?And tblUtility.Util ityID=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.DeleteObj ect acTable, "tblResults "
On Error GoTo 0
'CREATE TABLE SQL
TABLESQL = "CREATE TABLE tblResults ([Date] DATETIME, [ProductionVolum e] 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.Execu te TABLESQL
'INSERT INTO SQL
ECSQL = "INSERT INTO tblResults ([Date], [ProductionVolum e], [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.D ate, AVG(tblProducti on.ProductionVo lume) As ProductionVolum e, Sum(tblReading. ReadingVolume) AS [Usage] "
If chkBudgetUsage Then ECSQL = ECSQL & ", tblBudget.Budge t 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.D epartmentID = tblBudget.Depar tmentID) ON (tblAccounting. Date = tblProduction.D ate) AND (tblAccounting. Date = tblBudget.Date) ) INNER JOIN (tblReading INNER JOIN tblMeter ON tblReading.Mete rID = tblMeter.MeterI D) ON tblAccounting.D ate = tblReading.Date ) ON tblPeriod.Perio d = tblAccounting.P eriod) INNER JOIN tblTariff ON tblPeriod.Perio d = tblTariff.Perio d) ON (tblUtility.Uti lityID = tblTariff.Utili tyID) AND (tblUtility.Uti lityID = tblMeter.Utilit yID) AND (tblUtility.Uti lityID = tblBudget.Utili tyID) "
'GROUP BY
ECSQL = ECSQL & "GROUP BY tblAccounting.D ate, tblProduction.P roductionVolume , tblBudget.Budge t, tblProduction.D epartmentID, tblUtility.Util ityID "
'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.p eriod = " & DLookup("period ", "tblAccounting" , "date=#" & Format(Date, "dd/mmm/yyyy") & "#")
Case 3
'YEAR TO DATE
ECSQL = ECSQL & "HAVING right(tblAccoun ting.period,2) = " & Right(DLookup(" period", "tblAccounting" , "date=#" & Format(Date, "dd/mm/yyyy") & "#"), 2)
Case 4
'DATE RANGE
ECSQL = ECSQL & "HAVING tblAccounting.D ate BETWEEN #" & txtStart & "# AND #" & txtEnd & "#"
End Select
ECSQL = ECSQL & " AND ((tblProduction .DepartmentID)= 1) AND ((tblUtility.Ut ilityID)=1)"
'ECRecordSet.Op en ECSQL
CurrentDb.Execu te ECSQL
mysheetpath = "g:\Utilities\D atabase(2)\Data base\Department \charts.xls"
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel8, "tblResults ", mysheetpath, True, "output"
x = Shell("C:\Progr am Files\Microsoft Office\OFFICE11 \excel.exe g:\Utilities\Da tabase(2)\Datab ase\Department\ charts.xls", vbMaximizedFocu s)
Dim Xl As Excel.Applicati on
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Set Xl = CreateObject("E xcel.applicatio n")
Set XlBook = GetObject(myshe etpath)
Xl.Visible = True
XlBook.Windows( 1).Visible = True
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
Excel.Applicati on.Quit
End Sub
Comment