Date Lookup in VB

[JonathanParker]

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

[JonathanParker]

Any ideas anyone?