473,326 Members | 2,337 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Date Lookup in VB

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
Jul 12 '06 #1
1 3011
Any ideas anyone?
Jul 13 '06 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Si | last post by:
Hi there. I have a page on a website I am building where is want to look for all records added in the last 60 days. The date is added in short UK format, that is 25/12/2003 in an access...
1
by: fak | last post by:
I have a workorder entry form. There is a table that contains workorder information and another table that contains work dates that are closed (fully booked). When the workorder form is being...
2
by: Jonny | last post by:
Hello, I have a DB, which when I enter a date into the DateTo field, a box is populated with a Cut Off Date (this cut off date is held in a table against each week). For example, the table would...
4
by: Beejer | last post by:
I'm running Access 2003 on WinXP Here is my problem: I'm trying to lookup a date in a table from a query data. The query include the following: Query = Qry_Invoice and Table = Tbl_Friday ...
3
by: www.ttdown.com | last post by:
Each day a user is supposed to import a list of information into a database table. The user only has 30 days to import this information. After 30 days the information is lost. I need to be able...
5
by: NickCR_04 | last post by:
Hi all, I am (using ASP.net 2.0 in Visual Web developer 2005 Express Edit) trying to set up an asp front end to a music DB which has 2 main tables: 1) MUSIC - containing music release...
7
by: dunkleypilot | last post by:
Hello, I have created a database to track the flights on three aircraft owned by the flight school I manage. The relevant fields in the FlightRecords table are Date, AircraftID, HourMeterOut, and...
3
by: Harlequin | last post by:
I must start this posing by making the point that I am NOT a VB programmer and I'm something of a Newbie to MS Access. I can program in a number of languages (Java, Javascript, PERL,PHP and TCL) but...
3
aas4mis
by: aas4mis | last post by:
I have a form with a FSC field that has a percentage value. I'm wanting to fill that value with data from a "lookup" table. I'm aware of how to get this done in excell but am having problems with...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.