Pass variable from user input to query

[jmichaels]

Hello,



I'm trying to pass the month ending variable entered by the user to the query that I run at the end of the following vba code (Dim, user input & Do.cmd all in red lettering). It runs without an error, but the query does not return anything. When I remove the variable at the end of the OpenQuery statement, it does ask for the month ending and then returns the correct data. Does it have to do with dates needing to be enclosed in #s?:

Public Function importRPT0220A()


Dim MyArray As Variant
Dim fso As Variant
Dim objStream As Variant
Dim objFile As Variant
Dim sSQL As String
Dim rs As DAO.Recordset
Dim i As Integer
Dim strLine As String
Dim sCSVFile As String
Dim File As Object
Dim FileName As String
Dim FilePathName As String
Dim Path As String
Dim FileNameList() As String
Dim FileCount As Integer
Dim RemainType As String
Dim MonthEnding As Date
Dim MonthEndingMsg As String
Dim RecordImport As String
Dim FiscalPeriod As String
Dim FiscalPeriodMsg As String




FiscalPeriodMsg = "Enter folder name for fiscal period month#-month using format '##-MMM'"
FiscalPeriod = InputBox(Prompt:=FiscalPeriodMsg, title:="Fiscal Period Folder")


DoCmd.SetWarnings False
    Path = "N:\Corporate Accounting\Month-end Processing\Consumer\CDS Monthly Reporting\FY2021\" & FiscalPeriod & "\Club Sub RPT0220A\"
    FileName = Dir(Path & "")
    
MonthEndingMsg = "Enter month ending date."
MonthEnding = InputBox(Prompt:=MonthEndingMsg, title:="Month Ending")
    
    


While FileName <> "" And Right(FileName, 3) = "csv"
        FileCount = FileCount + 1
        ReDim Preserve FileNameList(1 To FileCount)
        FileNameList(FileCount) = FileName
        FileName = Dir()
    Wend
    
'initialize counter
'i = Nz(DMax("[ImportID]", "[RPT0220A]"), 0) + 1
    
sSQL = "SELECT * FROM RPT0220A;"
Set rs = CurrentDb.OpenRecordset("RPT0220A", dbOpenDynaset)


Set fso = CreateObject("Scripting.FileSystemObject")
    If FileCount > 0 Then
        For FileCount = 1 To UBound(FileNameList)
            FilePathName = Path & FileNameList(FileCount)
            Set objStream = fso.OpenTextFile(FilePathName, 1, False, 0)
            
 Do While Not objStream.AtEndOfStream
    strLine = objStream.ReadLine
       ReDim MyArray(0)
    'MyArray = Split(strLine, ",")
    MyArray = Split(Replace(strLine, Chr(34), ""), ",")
       
    If MyArray(0) = "   TOTAL DTP CASH" Then
         RecordImport = "Y"
      ElseIf MyArray(0) = "Category" Then
         RecordImport = "N"
    End If
    
 
    
    If MyArray(0) = "Category" Or RecordImport = "N" Or MyArray(0) = "" Then GoTo SKIP_FIRST_LINE
    On Error Resume Next
     rs.AddNew
     rs("[Category]") = MyArray(0)
     rs("[Date Range]") = MyArray(1)
     rs("Orders") = MyArray(2)
     rs("Copies") = MyArray(3)
     rs("Value") = MyArray(4)
     rs("Gross") = MyArray(5)
     rs("Value Less GST") = MyArray(6)
     rs("[GST]") = MyArray(7)
     rs("[Prov GST]") = MyArray(8)
     rs("[Magazine]") = MyArray(9)
     rs("[Report]") = MyArray(10)
     rs("[Run Date]") = MyArray(11)
     'rs("ImportID") = i
     rs("Month_Ending") = MonthEnding
     
     rs.Update
     
SKIP_FIRST_LINE:
    'i = i + 1
Loop


Next


End If
               
    
DoCmd.OpenQuery "RPT0220A-1_Append_Current_Month", MonthEnding
    
    DoCmd.SetWarnings True
    
     MsgBox "Done"
End Function

[June7]

Cannot pass filter criteria to query that way. None of the OpenQuery arguments allow for that. Did you read the intellisense tips as you type that command?

Why would you need to open a query object anyway?

Options for filtering:

1. apply filter criteria to form or report - review http://allenbrowne.com/ser-62.html

2. dynamic parameterized query object - also described in referenced link

3. VBA using QueryDefs to modify query object

[ranman256]

make a query then in the criteria put the user entry in side brackets :
[enter value here]

theres also no need to write code to do this, an update / append query will do this too.
move data from 1 query to a table.