Code:
Public Sub PTWeeklyReports(S As Long)
Dim SDate As Date, EDate As Date
Dim WAppS As String, ChemS As String, TimeS As String
Dim db As Database, qd1 As QueryDef, qd2 As QueryDef, qd3 As QueryDef
SDate = InputBox("Start Date", "Enter Date")
EDate = InputBox("End Date", "Enter Date", Date)
'Weekly Application Reports by date
WAppS = "SELECT OperationsT.OperationsID, OperationsT.OperationsDate, TruckT.TruckNumber, ContractingCompanyT.ContractingCompany, " & _
"OperationsT.City + ', ' + OperationsT.State AS CityState, " & _
"SupervisorT.FirstName + ' ' + SupervisorT.LastName AS Supervisor, " & _
"EmployeeT.ApplicatorNumber, OperationsT.TailgateDiscussion, OperationsT.AMWaterUsage, " & _
"OperationsT.PMWaterUsage, OperationsT.Footage, OperationsT.Width, SubstationT.SubStation, " & _
"OperationsT.LocationStart, OperationsT.LocationEnd, OperationsT.NatureOfBreakdown, " & _
"OperationsT.Comments, SubstationT.County, OperationsT.IsBilled, OperationsT.TotalUsage, " & _
"OperationsT.AMTime, OperationsT.PMTime, OperationsT.AMWind, OperationsT.PMWind, OperationsT.AMSpeed, " & _
"OperationsT.PMSpeed, OperationsT.AMTemperature, OperationsT.PMTemperature, OperationsT.AMGroundConditions, " & _
"OperationsT.PMGroundConditions, OperationsT.TimeStart, OperationsT.TimeStop, OperationsT.AMTravel, " & _
"OperationsT.PMTravel, OperationsT.TotalTime, OperationsT.OverTime, OperationsT.AcresSprayed, " & _
"OperationsT.GallonsPerAcre , EmployeeXOperationsT.PrimaryApplicator " & _
"FROM TruckT RIGHT JOIN SubstationT RIGHT JOIN ContractingCompanyT RIGHT JOIN OperationsT ON " & _
"ContractingCompanyT.ContractingCompanyID = OperationsT.ContractingCompanyID " & _
"ON SubstationT.SubstationID = OperationsT.SubstationID ON TruckT.TruckID = OperationsT.TruckID " & _
"LEFT JOIN SupervisorT ON OperationsT.SupervisorID = SupervisorT.SupervisorID " & _
"INNER JOIN EmployeeXOperationsT ON OperationsT.OperationsID = EmployeeXOperationsT.OperationsID " & _
"INNER JOIN EmployeeT ON EmployeeXOperationsT.EmployeeID = EmployeeT.EmployeeID " & _
"WHERE OperationsT.OperationsDate Between '" & SDate & "' And '" & EDate & "' " & _
"AND OperationsT.ContractingCompanyID=" & S & " " & _
"AND EmployeeXOperationsT.PrimaryApplicator=1 " & _
"ORDER BY OperationsT.OperationsDate ASC;"
'Chemical Subreport
ChemS = "SELECT OperationsT.OperationsID, ChemicalT.ChemicalName, ChemicalT.EPARegistration, ChemicalT.LotNumber, " & _
"ChemicalT.BatchNumber, ChemicalT.ActivePctPer100, CONVERT(varchar,Round(AMWaterUsage+PMWaterUsage*ActivePCTPer100*128,1)) & ' oz' " & _
"AS TotalChemUsage, OperationsT.AMWaterUsage, OperationsT.PMWaterUsage " & _
"FROM ChemicalT INNER JOIN RecipeT INNER JOIN RecipeXChemicalT ON " & _
"RecipeT.RecipeID = RecipeXChemicalT.RecipeID ON ChemicalT.ChemicalID = RecipeXChemicalT.ChemicalID " & _
"INNER JOIN OperationsT ON RecipeT.RecipeID = OperationsT.RecipeID " & _
"WHERE OperationsT.OperationsID=Reports!WeeklyApplicationR!OperationsID;"
'Employee Time Subreport
TimeS = "SELECT OperationsT.OperationsID, EmployeeT.FirstName & ' ' & EmployeeT.LastName " & _
"AS EmployeeName, EmployeeXOperationsT.PrimaryApplicator, EmployeeT.Wage, OperationsT.TimeStart, " & _
"OperationsT.TimeStop, OperationsT.AMTravel, OperationsT.PMTravel, OperationsT.TotalTime, OperationsT.Overtime " & _
"FROM OperationsT INNER JOIN EmployeeT INNER JOIN EmployeeXOperationsT ON " & _
"EmployeeT.EmployeeID = EmployeeXOperationsT.EmployeeID ON OperationsT.OperationsID = EmployeeXOperationsT.OperationsID " & _
"WHERE OperationsT.OperationsID=Reports!WeeklyApplicationR!OperationsID;"
On Error GoTo ErrMsg
Set db = CurrentDb
Set qd1 = db.QueryDefs("PTApplicationQ")
qd1.Connect = ConString
qd1.SQL = WAppS
Set qd2 = db.QueryDefs("PTChemicalQ")
qd2.Connect = ConString
qd2.SQL = ChemS
Set qd3 = db.QueryDefs("PTTimeQ")
qd3.Connect = ConString
qd3.SQL = TimeS
With DoCmd
.Echo False
.OpenReport "WeeklyApplicationR", acViewReport
Reports!WeeklyApplicationR.Report.RecordSource = "PTApplicationQ"
Reports!WeeklyApplicationR!WeeklyChemicalR.Report.RecordSource = "PTChemicalQ"
Reports!WeeklyApplicationR!WeeklyTimeR.Report.RecordSource = "PTTimeQ"
.OpenReport "WeeklyApplicationR", acViewPreview
.Echo True
End With
KillSub:
qd1.Close
Set qd1 = Nothing
qd2.Close
Set qd2 = Nothing
qd3.Close
Set qd3 = Nothing
Exit Sub
ErrMsg:
ErrorMsg 'global error message sub to reduce keystrokes
Resume KillSub
End Sub