The is the main part of the code:
Code:
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim table_name As String
Dim strQuery As String
Dim fields As String
Dim c As Integer
Dim XL As Excel.Application
Dim WB As Excel.Workbook
Dim WKS As Excel.Worksheet
Dim i As Integer
Dim lastrow As Integer
Dim lastcol As Integer
' ************** to rename file when extract complete ************
Dim oldname As String
Dim newname As String
'*********************************************************************
' Need to fix the order of the process
' First create SQL qry to get data
' Second create Excel workbook
' Third step through data
' Fourth if route not current create new sheet & Name
' Fifth add data to sheet
' Sixth step back to Fourth entil EOF
' Seventh TBD
' Eighth sum up quarters
' Ninth put on cover page (in front of others) with totals
' Tenth ask to keep or delete
'*************** Run Query for Input *******************************
MsgBox (CurrentDb.Name) ' <---- shows correct db name
Set dbs = CurrentDb
Set rs = CurrentDb.OpenRecordset(qryQtrSum)
'************ Setup Excel ************************
When I run the query standalone it works. When I run it with the "Set rs = CurrentDB.OpenRecordset(qryQtrSum)" I get the error that says it can't find the database and/or the query. The msgbox shows the current database correctly. The query is correct also. The following is the query:
Code:
SELECT tblReceipt.Route
, tblReceipt.Driver
, Format([tblReceipt].[date],"ddd") AS [Day]
, Count(tblReceipt.Route) AS DCount
, "Sweep" AS DIV
, Sum(tblReceipt.TotalHours) AS TH
, Sum(tblReceipt.RecActHrs) AS TR
, [th]-[tr] AS TD
, Sum(tblReceipt.TotalDollar) AS SumOfTotalDollar
, Avg(tblReceipt.TotalHours) AS ATH
, Avg(tblReceipt.RecActHrs) AS ARH
, [td]/[dcount] AS AD
, [sumoftotaldollar]/[th] AS APH
FROM tblReceipt INNER JOIN
tblRoutes ON tblReceipt.Route = tblRoutes.Route
WHERE ((tblReceipt.Helper1) Is Null)
AND ((tblReceipt.Helper2) Is Null)
AND ((tblReceipt.Training) Is Null)
AND ((tblRoutes.Division)="sweep")
GROUP BY tblReceipt.Route
, tblReceipt.Driver
, Format([tblReceipt].[date],"ddd")
, "Sweep"
HAVING (((Count(tblReceipt.Route))>0))
UNION ALL SELECT tblLIReceipt.Route
, tblLIReceipt.Driver
, Format([tblLIReceipt].[date],"ddd") AS [Day]
, Count(tblLIReceipt.Route) AS DCount
, "Interior" AS DIV
, Sum(tblLIReceipt.TotalHours) AS TH
, Sum(tblLIReceipt.RecActHrs) AS TR
, [th]-[tr] AS TD
, Sum(tblLIReceipt.TotalDollar) AS SumOfTotalDollar
, Avg(tblLIReceipt.TotalHours) AS ATH
, Avg(tblLIReceipt.RecActHrs) AS ARH
, [td]/[dcount] AS AD
, [sumoftotaldollar]/[th] AS APH
FROM tblRoutes INNER JOIN
tblLIReceipt ON tblRoutes.Route = tblLIReceipt.Route
WHERE ((tblLIReceipt.Helper1) Is Null)
AND ((tblLIReceipt.Helper2) Is Null)
AND ((tblRoutes.Division)="Interior")
GROUP BY tblLIReceipt.Route
, tblLIReceipt.Driver
, Format([tblLIReceipt].[date],"ddd")
, "Interior"
HAVING (((Count(tblLIReceipt.Route))>0));
And the query is spelled correctly. I'm really lost now. This is the latest.
************************************************** *********************************
**** I GOT IT!!!!! The query needs to be within quotes!!!!! Thanks AJAX for all your patience and help.
**** I now have a record set I can step through and filter by date. YEA!!!!!
************************************************** *********************************
Windows 10 
Access 2010 32bit
Reply With Quote
Originally Posted by JimReid
