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?:
Code:
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