Programming SQL with dates in VBA

G

Guest

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 would be greatly appreciated.

Also, I'm having some trouble with Excel. I am exporting data from Access
to Excel to create some decent graphs, however, when I close Excel and try to
run it again, it says that Excel is still running. Althought there's nothing
in the TaskManager Applications pane when I look in the Processes pane Excel
is still runnning. I've tried a few different things to solve this but I'm
all out of ideas. I'm quite new to this so any help would 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\chart s.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
 
R

RoyVidar

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 would be greatly appreciated.

Also, I'm having some trouble with Excel. I am exporting data from
Access to Excel to create some decent graphs, however, when I close
Excel and try to run it again, it says that Excel is still running.
Althought there's nothing in the TaskManager Applications pane when
I look in the Processes pane Excel is still runnning. I've tried a
few different things to solve this but I'm all out of ideas. I'm
quite new to this so any help would 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\chart
s.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
Click to expand...

Lets have a look at the dates first. When "speaking" to Jet through
dynamic sql, you need to pass it in an unambiguous format. This means
for instance ISO 8601 "yyyy-mm-dd" or US format. Note also for US
format, that you might need escaping it "mm\/dd\/yyyy".

See for instance http://allenbrowne.com/ser-36.html for more info.
 
G

Guest

Thanks for your swift response Roy. Originally I forgot to format the dates
into the UK format as required and I was still faced with the same error.
I've been told it is something to do with the use of the GROUP BY and HAVING
statements, and because of a lack of an appropriate WHERE statement in the
correct place. I've been swotting up on this for two days now and I still
can't find a way round it. : ( If you or anyone else thinks they know of a
way round this I'd love to hear them.

Thanks

JP

RoyVidar said:
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 would be greatly appreciated.

Also, I'm having some trouble with Excel. I am exporting data from
Access to Excel to create some decent graphs, however, when I close
Excel and try to run it again, it says that Excel is still running.
Althought there's nothing in the TaskManager Applications pane when
I look in the Processes pane Excel is still runnning. I've tried a
few different things to solve this but I'm all out of ideas. I'm
quite new to this so any help would 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\chart
s.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
Click to expand...

Lets have a look at the dates first. When "speaking" to Jet through
dynamic sql, you need to pass it in an unambiguous format. This means
for instance ISO 8601 "yyyy-mm-dd" or US format. Note also for US
format, that you might need escaping it "mm\/dd\/yyyy".

See for instance http://allenbrowne.com/ser-36.html for more info.
Click to expand...
 
D

Douglas J Steele

PMFJI. I haven't read the whole thread, but I though I'd comment on your
"forgot to format the dates into the UK format" statement.

In SQL, you do NOT want to use dd/mm/yyyy format for dates: it won't work
until after the 12th day of the month. Regardless of what you've set the
Short Date format to in Regional Settings, Access will always assume
mm/dd/yyyy, unless that results in an invalid date (hence my comment of
"after the 12th day of the month": since there are only 12 months, when
Access encounters 13 or higher in the first 2 positions, it will then try
dd/mm/yyyy format)

You might want to read Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I had in my September 2003 Access
Answers column for Pinnacle Publication's "Smart Access" newsletter. (The
column and accompanying database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JP said:
Thanks for your swift response Roy. Originally I forgot to format the dates
into the UK format as required and I was still faced with the same error.
I've been told it is something to do with the use of the GROUP BY and HAVING
statements, and because of a lack of an appropriate WHERE statement in the
correct place. I've been swotting up on this for two days now and I still
can't find a way round it. : ( If you or anyone else thinks they know of a
way round this I'd love to hear them.

Thanks

JP

RoyVidar said:
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 would be greatly appreciated.

Also, I'm having some trouble with Excel. I am exporting data from
Access to Excel to create some decent graphs, however, when I close
Excel and try to run it again, it says that Excel is still running.
Althought there's nothing in the TaskManager Applications pane when
I look in the Processes pane Excel is still runnning. I've tried a
few different things to solve this but I'm all out of ideas. I'm
quite new to this so any help would 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\chart
s.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
Click to expand...

Lets have a look at the dates first. When "speaking" to Jet through
dynamic sql, you need to pass it in an unambiguous format. This means
for instance ISO 8601 "yyyy-mm-dd" or US format. Note also for US
format, that you might need escaping it "mm\/dd\/yyyy".

See for instance http://allenbrowne.com/ser-36.html for more info.
Click to expand...
Click to expand...
 
R

RoyVidar

Thanks for your swift response Roy. Originally I forgot to format
the dates into the UK format as required and I was still faced with
the same error.
Click to expand...

Please, again - it cannot be UK format, it must be either US, ISO 8601
or another unambiguous format, it cannot be UK format!
I've been told it is something to do with the use
of the GROUP BY and HAVING statements, and because of a lack of an
appropriate WHERE statement in the correct place. I've been
swotting up on this for two days now and I still can't find a way
round it. : ( If you or anyone else thinks they know of a way round
this I'd love to hear them.

Thanks

JP
Click to expand...

I'm not very good at SQL, but when the date formats are OK, do a
debug.print of the ESQL just before you execute it. The resulting,
string (ctrl+g), you should be able to past into the SQL view of the
query builder, and check.

In your HAVING clause, you refer to the field Period, which isn't
referred to in the GROUP BY clause. This might be the reason for the
error message.

I think you could replace your HAVING clause with a WHERE clause.
 
G

Guest

Thanks for the help guys. I'm gonna give this a try in the office today.
I'll let you know how it goes.

JP
 
R

RoyVidar

Gave it a try but to no avail. Any other suggestions?
:
Click to expand...

Without seeing what you've tried now, the result of the debug.print,
the errormessages, I have no suggestions. Perhaps others have?
 
O

onedaywhen

JP said:
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.'
Click to expand...

Please post the plain text SQL that your code generates.

TIA,
Jamie.

--
 
G

Guest

onedaywhen said:
Please post the plain text SQL that your code generates.

TIA,
Jamie.
Click to expand...

Here is the code I got from Access' design view

INSERT INTO tblResults ( [Date], ProductionVolume, [Usage], BudgetUsage,
ActualCost, BudgetCost )
SELECT DISTINCT tblAccounting.Date, tblProduction.ProductionVolume,
tblReading.ReadingVolume AS [Usage], tblBudget.Budget AS BudgetUsage,
Sum([tblreading].[readingvolume]*[tbltariff].[tariff]) AS ActualCost,
Sum([tblbudget].[budget]*[tblTariff].[Tariff]) AS BudgetCost
FROM tblUtility INNER JOIN ((tblPeriod INNER JOIN ((tblAccounting INNER JOIN
(tblBudget INNER JOIN tblProduction ON tblBudget.DepartmentID =
tblProduction.DepartmentID) ON (tblAccounting.Date = tblBudget.Date) AND
(tblAccounting.Date = tblProduction.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 =
tblBudget.UtilityID) AND (tblUtility.UtilityID = tblMeter.UtilityID) AND
(tblUtility.UtilityID = tblTariff.UtilityID)
GROUP BY tblAccounting.Date, tblReading.ReadingVolume, tblBudget.Budget,
tblProduction.ProductionVolume, tblPeriod.Period, tblProduction.DepartmentID,
tblUtility.UtilityID

Here, depending on which option is selected on the form, there are a number
of different HAVING caluses employed, the VB code is here

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 #" &
Format(txtStart, "dd/mm/yyyy") & "# AND #" & Format(txtEnd, "dd/mm/yyyy") &
"#"
End Select

ECSQL = ECSQL & "(((tblProduction.DepartmentID)=1) AND
((tblUtility.UtilityID)=1))"


I haven't determined how to replicate this VB code in design view yet, I'm
quite new to all this and really need some help!

Thanks

JP
 
O

onedaywhen

JP said:
I haven't determined how to replicate this VB code in design view yet
Click to expand...

Hault execution within your Select Case Frame40 subroutine e.g. by
putting a breakpoint on the End Select, then get the contents of you
ECSQL variable i.e. the one that is giving you the error.

Jamie.

--
 
G

Guest

onedaywhen said:
Hault execution within your Select Case Frame40 subroutine e.g. by
putting a breakpoint on the End Select, then get the contents of you
ECSQL variable i.e. the one that is giving you the error.

Jamie.
Click to expand...

Sorry, what do you mean by get the contents? What do I have to do with the
contents, put it back into design view? I've only been doing this for about
four weeks so you'll have to excuse me.
 
O

onedaywhen

JP said:
Sorry, what do you mean by get the contents? What do I have to do with the
contents
Click to expand...

At the 'bottom' of your code. include the line

Debug.Print ECSQL

Navigate to the Immediate Window in the Visual Basic Editor, copy the
text and post it here.

Jamie.

--
 
G

Guest

onedaywhen said:
At the 'bottom' of your code. include the line

Debug.Print ECSQL

Navigate to the Immediate Window in the Visual Basic Editor, copy the
text and post it here.

Jamie.
Click to expand...

Got this in the immediate window.

INSERT INTO tblResults ([Date], [ProductionVolume], [Usage], [BudgetUsage],
[ActualCost], [BudgetCost]) SELECT DISTINCT tblAccounting.Date,
AVG(tblProduction.ProductionVolume) As ProductionVolume,
Sum(tblReading.ReadingVolume) AS [Usage] , tblBudget.Budget AS BudgetUsage ,
Sum([tblreading].[readingvolume]*[tbltariff].[tariff]) AS ActualCost ,
Avg([tblbudget].[budget]*[tblTariff].[Tariff]) AS BudgetCost 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 = tblBu
dget.UtilityID) GROUP BY tblAccounting.Date, tblProduction.ProductionVolume,
tblBudget.Budget, tblProduction.DepartmentID, tblUtility.UtilityID HAVING
(tblAccounting.period) = 406 AND ((tblProduction.DepartmentID)=1) AND
((tblUtility.UtilityID)=1)
 
G

Guest

onedaywhen said:
At the 'bottom' of your code. include the line

Debug.Print ECSQL

Navigate to the Immediate Window in the Visual Basic Editor, copy the
text and post it here.

Jamie.
Click to expand...

In the other query when get all records for year to date I get this in the
immediate window.

INSERT INTO tblResults ([Date], [ProductionVolume], [Usage], [BudgetUsage],
[ActualCost], [BudgetCost]) SELECT DISTINCT tblAccounting.Date,
AVG(tblProduction.ProductionVolume) As ProductionVolume,
Sum(tblReading.ReadingVolume) AS [Usage] , tblBudget.Budget AS BudgetUsage ,
Sum([tblreading].[readingvolume]*[tbltariff].[tariff]) AS ActualCost ,
Avg([tblbudget].[budget]*[tblTariff].[Tariff]) AS BudgetCost 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 = tblBu
dget.UtilityID) GROUP BY tblAccounting.Date, tblProduction.ProductionVolume,
tblBudget.Budget, tblProduction.DepartmentID, tblUtility.UtilityID HAVING
right(tblAccounting.period,2) = 06 AND ((tblProduction.DepartmentID)=1) AND
((tblUtility.UtilityID)=1)
 
O

onedaywhen

JP said:
SELECT DISTINCT tblAccounting.Date,
AVG(tblProduction.ProductionVolume) As ProductionVolume,
Sum(tblReading.ReadingVolume) AS [Usage] , tblBudget.Budget AS BudgetUsage ,
Sum([tblreading].[readingvolume]*[tbltariff].[tariff]) AS ActualCost ,
Avg([tblbudget].[budget]*[tblTariff].[Tariff]) AS BudgetCost FROM
<<snipped>>
HAVING
right(tblAccounting.period,2) = 06 AND ((tblProduction.DepartmentID)=1) AND
((tblUtility.UtilityID)=1)
Click to expand...

OK, I think the problem is you are referring to columns in your HAVING
clause that do not appear in your SELECT clause. The fix would be to
move the search criteria from the HAVING clause to the WHERE clause.

Jamie.

--
 
Top