Code:
Private Sub btnDetailedBilling_Click()
Dim GroupS As String, DBillingS As String, WAppS As String
Dim UpBill As String, AppBill As String, AppTotal As String
SDate = InputBox("Start Date", "Enter Date")
EDate = InputBox("End Date", "Enter Date", Date)
WAppS = "SELECT WeeklyOperationsQ.OperationsID, WeeklyOperationsQ.OperationsDate, TruckT.TruckNumber, ContractingCompanyT.ContractingCompany, " & _
"[WeeklyOperationsQ].[City] & ', ' & [WeeklyOperationsQ].[State] AS CityState, [SupervisorT].[FirstName] & ' ' & [SupervisorT].[LastName] AS Supervisor, " & _
"WeeklyOperationsQ.TailgateDiscussion, WeeklyOperationsQ.AMWaterUsage, WeeklyOperationsQ.PMWaterUsage, WeeklyOperationsQ.Footage, WeeklyOperationsQ.Width, " & _
"SubstationT.SubStation, WeeklyOperationsQ.TotalAcres, WeeklyOperationsQ.CalcGPA, WeeklyOperationsQ.LocationStart, WeeklyOperationsQ.LocationEnd, " & _
"WeeklyOperationsQ.NatureOfBreakdown, WeeklyOperationsQ.Comments, WeatherT.AMTime, WeatherT.PMTime, WeatherT.AMWind, WeatherT.PMWind, WeatherT.AMSpeed, " & _
"WeatherT.PMSpeed, WeatherT.AMTemperature, WeatherT.PMTemperature, WeatherT.AMGroundConditions, WeatherT.PMGroundConditions, SubstationT.County, " & _
"[AMWaterUsage]+[PMWaterUsage] & 'gal' AS TotalWaterUsage, WeeklyApplicatorNumberQ.ApplicatorNumber, WeeklyOperationsQ.OperationsT.IsBilled " & _
"FROM WeeklyApplicatorNumberQ AS WeeklyApplicatorNumberQ_1 INNER JOIN (WeeklyApplicatorNumberQ INNER JOIN (((SubstationT INNER JOIN " & _
"(SupervisorT INNER JOIN (TruckT INNER JOIN WeeklyOperationsQ ON (TruckT.TruckID = WeeklyOperationsQ.TruckID) AND " & _
"(TruckT.TruckID = WeeklyOperationsQ.TruckID)) ON SupervisorT.SupervisorID = WeeklyOperationsQ.SupervisorID) " & _
"ON SubstationT.SubstationID = WeeklyOperationsQ.SubstationID) INNER JOIN WeatherT ON WeeklyOperationsQ.OperationsID = WeatherT.OperationsID) " & _
"INNER JOIN ContractingCompanyT ON WeeklyOperationsQ.[ContractingCompanyID] = ContractingCompanyT.ContractingCompanyID) " & _
"ON WeeklyApplicatorNumberQ.OperationsID = WeeklyOperationsQ.OperationsID) ON WeeklyApplicatorNumberQ_1.OperationsID = WeeklyOperationsQ.OperationsID " & _
"WHERE WeeklyOperationsQ.OperationsDate Between #" & SDate & "# And #" & EDate & "# AND WeeklyOperationsQ.OperationsT.IsBilled=False;"
DBillingS = "SELECT OperationsT.OperationsID, OperationsT.OperationsDate, SubstationT.SubStation, TruckT.TruckNumber, OperationsT.Footage, OperationsT.Width, " & _
"OperationsT.Billing, OperationsT.IsBilled, ContractingCompanyT.ContractingCompany, ContractingCompanyT.ContractingCompanyID " & _
"FROM TruckT INNER JOIN ((SubstationT INNER JOIN ContractingCompanyT ON SubstationT.ContractingCompanyID = ContractingCompanyT.ContractingCompanyID) " & _
"INNER JOIN OperationsT ON (SubstationT.SubstationID = OperationsT.SubstationID) AND " & _
"(ContractingCompanyT.ContractingCompanyID = OperationsT.ContractingCompanyID)) ON TruckT.TruckID = OperationsT.TruckID " & _
"WHERE OperationsT.OperationsDate Between #" & SDate & "# And #" & EDate & "# AND OperationsT.IsBilled=False;"
GroupS = "SELECT BillingQ.SubStation, Sum(BillingQ.Footage) AS SumOfFootage, Sum(BillingQ.Billing) AS SumOfBilling " & _
"FROM BillingQ " & _
"WHERE BillingQ.OperationsDate Between #" & SDate & "# And #" & EDate & "# " & _
"GROUP BY BillingQ.SubStation;"
UpBill = "UPDATE OperationsT SET OperationsT.IsBilled = True " & _
"WHERE OperationsT.OperationsDate Between #" & SDate & "# And #" & EDate & "# AND OperationsT.IsBilled=False;"
AppBill = "INSERT INTO BilledT ( SubstationID, Footage, Billing, ContractingCompanyID ) " & _
"SELECT OperationsT.SubstationID, OperationsT.Footage, OperationsT.Billing, OperationsT.ContractingCompanyID " & _
"FROM OperationsT " & _
"WHERE OperationsT.OperationsDate Between #" & SDate & "# And #" & EDate & "# AND OperationsT.IsBilled=False;"
AppTotal = "INSERT INTO TotalBilledT ( Billed, ContractingCompanyID ) " & _
"SELECT Sum(BilledT.Billing) AS SumOfBilling, BilledT.ContractingCompanyID " & _
"FROM BilledT " & _
"GROUP BY BilledT.ContractingCompanyID, BilledT.IsBilled " & _
"HAVING (((BilledT.IsBilled)=False));"
If MsgBox("You are about to run billing on operations between " & SDate & " and " & EDate & vbNewLine & _
"This can not be reversed. Click YES to run, Click NO to view only", vbYesNo + vbExclamation, "Run Billing") = vbYes Then
DoCmd.OpenReport "WeeklyApplicationR", acViewReport
Reports!WeeklyApplicationR.Report.RecordSource = WAppS
DoCmd.OpenReport "DetailedBillingR", acViewReport
Reports!DetailedBillingR.Report.RecordSource = DBillingS
DoCmd.OpenReport "GroupedBillingR", acViewReport
Reports!GroupedBillingR.Report.RecordSource = GroupS
DoCmd.RunSQL UpBill
DoCmd.RunSQL AppBill
DoCmd.RunSQL AppTotal
DoCmd.OpenQuery "UpdateBilledQ"
Else
DoCmd.OpenReport "WeeklyApplicationR", acViewReport
Reports!WeeklyApplicationR.Report.RecordSource = WAppS
DoCmd.OpenReport "DetailedBillingR", acViewReport
Reports!DetailedBillingR.Report.RecordSource = DBillingS
DoCmd.OpenReport "GroupedBillingR", acViewReport
Reports!GroupedBillingR.Report.RecordSource = GroupS
End If
End Sub