Results 1 to 13 of 13
  1. #1
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87

    Can't get Append Query to run


    I have this SQL statement Dim'ed as a string
    Code:
        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;"
    I can get to work just fine until I add the second WHERE condition of IsBilled=False. This is part of a larger subroutine that runs five consecutive queries based upon Between Date inputs. I've set the date inputs as a variable so I only have to input them once instead of five times. Do any of you SQL masters out there have any advice? TIA

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    run the data sheet (select query) does it return values to append?

  3. #3
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    Yes, if I run the datasheet it runs fine. The problem is not being able to use my SDate and Edate variables in the datasheet (not that I know how to do), which requires me to input the Start and End dates again

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    do the 2 dates have values?

    can you use form textboxes? then its:

    Between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SQL looks fine to me. IsBilled is a yes/no type field? It works in SELECT query?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    Let me post the entire sub. SDate and Edate are Globals

    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

  7. #7
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87

    DB File

    I think i properly attached it

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Really should have code to validate inputs.

    I still can't see anything wrong. Would have to analyze db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You seem to be saying now that dates are the problem,
    The problem is not being able to use my SDate and Edate variables in the datasheet
    but at first you said
    I can get to work just fine until I add the second WHERE condition of IsBilled=False
    You're also sort of saying the query "doesn't work" which doesn't reveal much. Doesn't run? Raises error? Not the right records? Query isn't the problem, it's the code?
    The select portion of it runs fine if I enter 02/02/2022 and accept the end date presented. 25 records are returned, but that seems to be 2 records short and there's no Distinct predicate in your coded sql, so I dunno about that.

    EDIT - no record count issue. It's just the date span at play.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    I figured it out. I was updating records as billed before appending the unbilled records. So when it came time to append the unbilled records, they were already marked as billed. Simple mistake

    Code:
       DoCmd.RunSQL UpBill        
                 DoCmd.RunSQL AppBill
                 DoCmd.RunSQL AppTotal
                DoCmd.OpenQuery "UpdateBilledQ"

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Easy enough to do! Future tip:
    instead of writing something multiple times, consider
    Dim strWhere As String

    strWhere = "WHERE WeeklyOperationsQ.OperationsDate Between #" & SDate & "# And #" & EDate & "# AND WeeklyOperationsQ.OperationsT.IsBilled=False;"

    ...ON WeeklyApplicatorNumberQ_1.OperationsID = WeeklyOperationsQ.OperationsID " & _
    strWhere

    IMO it's a good way of piecing sql together when it might or might not contain Where or Order By clauses or other criteria might be optional.
    Last edited by Micron; 04-20-2022 at 02:53 PM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    Thanks, I'll try and remember that. I've been using Access for about 10 months and VBA for 7. I still have much to learn but I'm amazed at all the cool stuff I can do with it

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm amazed at all the cool stuff I can do with it
    You have no idea! If you can think of it, there's probably a code library reference for it. By that, I mean the .dll's, .tlb's, etc. that are used in a code project references. My last big surprise was learning about WIA (Windows Image Acquisition, I think) library, which allows you to rotate, flip and do other things with image files.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-03-2021, 08:32 AM
  2. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  3. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  4. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  5. Replies: 7
    Last Post: 07-21-2011, 01:01 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums