Adding parameter to existing code to export an Acc

Question

'Below is the current code that I have and it will export to the excel workbook and worksheet correctly. The only problem is that I need to limit the data that gets exported by a month end date range (example: 1/31/2017 to 4/30/2017) and also by a plant number (example: "4101") thanks for any help it is greatly appreciated.

Public Function InventoryXport_4100()

Dim appXL As Object
Dim wb As Object
Dim wks As Object
Dim xlf As String
Dim rs As DAO.Recordset
Dim fld As Field
Dim intColCount As Integer

xlf = "Z:\COST ACCOUNTING INFO\Inventory Reports\MyFile.xlsx"

Set rs = CurrentDb.OpenRecordset("(QS)_Inventory") 
Set appXL = CreateObject("Excel.Application")
Set wb = appXL.Workbooks.Open(xlf)
Set wks = wb.Sheets("Inventory Xport") 'Sheet name

If rs.EOF = True Then
  MsgBox "No data", vbOKOnly
Exit Function
End If

With appXL
  .Application.worksheets("Inventory Xport").SELECT
  .Application.columns("A:AQ").SELECT
  .Application.columns.Clear
End With

intColCount = 1

For Each fld In rs.Fields
  wks.Cells(1, intColCount).Value = fld.Name
  intColCount = intColCount + 1
Next fld

appXL.displayalerts = False

wks.Range("A2").CopyFromRecordset rs

appXL.Visible = True

With appXL
  .Application.worksheets("Inventory Xport").SELECT
  .Application.columns("A:AQ").SELECT
  .Application.columns.AutoFit
  .Application.Range("A2").SELECT
  .Application.ActiveWindow.FreezePanes = True
End With

wb.Save
wb.Close
appXL.Quit

Set wb = Nothing
 rs.Close
Set rs = Nothing

End Function

Answer 1

You need to put the filter conditions into a where clause on your query:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [(QS)_Inventory] WHERE ...")

If you need help with the syntax of the where clause you will need to post the structure of your query (i.e. at least give the names and data types of the columns that you want to filter on). Also, if you want to pass these parameters into the function then you'll need to include them in the Function declaration with suitable parameter names, and replacing each ???? with the appropriate data type:

Public Function InventoryXport_4100(prm1 As ????, prm2 As ????, prm3 As ????)

By the way, why is this a Function rather than a Proc - there's no return value or even return data type set?

Edit following comments back and forth:

So try something like

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [(QS)_Inventory] " & _
        "WHERE [Plant] = '4101' " & _
        "AND [Per] Between #01/31/2017# And #04/30/2017#")