Results 1 to 3 of 3
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Exporting to Excel

    I am trying to export 2 queries from Access to Excel. The first one is functioning properly, the second one is not transferring anything. Here is the code.



    Code:
    Private Sub cmdGo1_Click() ' Contract Report - Invoicing
    Dim CountProc As Integer
    Dim strRptSQL1 As String  ' Itemized Query
    Dim strRptSQL2 As String  ' Defect Query
    Dim strContractInvoiceSQL As String
    Dim strDefectSummarySQL As String
    Dim db As DAO.Database
    Dim rsQuery As Recordset
    Dim rsQuery2 As Recordset
    Set db = CurrentDb
    dt1 = Me.tbxDate1
    dt2 = Me.tbxDate2
    
    
        strRptSQL1 = "SELECT WOTracking.OrderNo, WOTracking.SKU, WOTracking.Date_Time, WOTracking.SKUQty, WOTracking.Process, Inventory.BoxingType FROM WOTracking INNER JOIN Inventory ON WOTracking.SKU = Inventory.SKU WHERE ContractCo = '" & Me.cbxContractCo & "' AND  WOTracking.Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND WOTracking.Date_Time <#" & Format(dt2, "yyyy-mm-dd") & "# ORDER BY OrderNo;"
        strRptSQL2 = "SELECT DefectEvents.OrderNumber, DefectEvents.SKU, DefectEvents.Date_Time, DefectEvents.DefectQty, DefectEvents.Category, DefectEvents.Process, Inventory.ReplaceCost, Inventory.BoxingType FROM DefectEvents INNER JOIN Inventory ON DefectEvents.SKU = Inventory.SKU WHERE DefectEvents.Marketplace = '" & Me.cbxContractCo & "' AND  DefectEvents.Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND DefectEvents.Date_Time <#" & Format(dt2, "yyyy-mm-dd") & "# ORDER BY DefectEvents.SKU;"
        Debug.Print strRptSQL1
        Debug.Print strRptSQL2
        
        Set rsQuery = db.OpenRecordset(strRptSQL1)
        Set rsQuery2 = db.OpenRecordset(strRptSQL2)
        
        Set excelApp = CreateObject("Excel.application", "")
        excelApp.Visible = True
        Set targetWorkbook = excelApp.workbooks.Open("J:\Databases-DO NOT MOVE\LE Tracking System\InvoicingReport.xlsx")
        
        targetWorkbook.Worksheets("InvoiceRaw").Range("A2").CopyFromRecordset rsQuery
        strContractInvoiceSQL = "SELECT * FROM InvoiceExport WHERE OrderNo <> '0'"
        DoCmd.OpenReport "Contract Invoice", acViewPreview, strContractInvoiceSQL
        
        targetWorkbook.Worksheets("DefectRaw").Range("A2").CopyFromRecordset rsQuery2
        strDefectSummarySQL = "SELECT * FROM DefectExport WHERE SKU <> '0'"
        DoCmd.OpenReport "Invoice Summary", acViewPreview, strDefectSummarySQL
     
    End Sub
    Debug.Print for the SQL statements are:

    Code:
    SELECT WOTracking.OrderNo, WOTracking.SKU, WOTracking.Date_Time, WOTracking.SKUQty, WOTracking.Process, Inventory.BoxingType FROM WOTracking INNER JOIN Inventory ON WOTracking.SKU = Inventory.SKU WHERE ContractCo = 'Contract - VendorJump' AND  WOTracking.Date_Time >= #2019-05-01# AND WOTracking.Date_Time <#2019-05-29# ORDER BY OrderNo;
    SELECT DefectEvents.OrderNumber, DefectEvents.SKU, DefectEvents.Date_Time, DefectEvents.DefectQty, DefectEvents.Category, DefectEvents.Process, Inventory.ReplaceCost FROM DefectEvents INNER JOIN Inventory ON DefectEvents.SKU = Inventory.SKU WHERE DefectEvents.Marketplace = 'Contract - VendorJump' AND  DefectEvents.Date_Time >= #2019-05-01# AND DefectEvents.Date_Time <#2019-05-29# ORDER BY DefectEvents.SKU;
    I am sure I am missing something obvious, but just can't put my finger on it. As always, any help would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Take that constructed SQL statement and copy/paste to Access query object. Are records returned?
    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.

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    It wasn't. I found the issue. It was a data issue (one table with a space, the other without). Thanks for making me look at it from a different angle.

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

Similar Threads

  1. Exporting to Excel
    By JoeM in forum Import/Export Data
    Replies: 15
    Last Post: 09-15-2017, 08:56 AM
  2. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  3. Exporting to Excel
    By RAMSES955i in forum Import/Export Data
    Replies: 1
    Last Post: 07-04-2012, 02:06 PM
  4. exporting to Excel
    By johnririe in forum Import/Export Data
    Replies: 6
    Last Post: 08-23-2011, 10:16 AM
  5. Exporting to Excel
    By DreamOn in forum Import/Export Data
    Replies: 1
    Last Post: 05-23-2010, 10:27 PM

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