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.