Hello,
I'm using the following code to 1) import all csv tables that exist in a folder into table RPT0185, 2) ask user for month ending date (which doesn't exist in the individual csv tables), and 3)update the Month_Ending field in the table with the user input. It runs through, but when the user inputs 4/30/2020 (as an example) it shows up in the table as 12/30/1899. The Month_Ending field in the table is defined as data type Date/Time with format Short Date. I tried both the current and commented out command below with the same results (in red). Also, unrelated question...when should I use Public Sub vs Public Function?
Public Sub importRPT0185()
Dim FileName, FilePathName, Path, FileNameList() As String
Dim FileCount As Integer
Dim MonthEnding As Date
Dim MonthEndingMsg As String
DoCmd.SetWarnings False
Path = "C:\Users\jamichaels\OneDrive - Highlights Family of Companies\Documents\Magazine JE\Current RPT0185A"
FileName = Dir(Path & "")
While FileName <> "" And Right(FileName, 3) = "csv"
FileCount = FileCount + 1
ReDim Preserve FileNameList(1 To FileCount)
FileNameList(FileCount) = FileName
FileName = Dir()
Wend
If FileCount > 0 Then
For FileCount = 1 To UBound(FileNameList)
FilePathName = Path & FileNameList(FileCount)
DoCmd.TransferText transferType:=acImportDelim, TableName:="RPT0185", FileName:=FilePathName, hasfieldnames:=True
Next
End If
MonthEndingMsg = "Enter month ending date."
MonthEnding = InputBox(Prompt:=MonthEndingMsg, title:="Month Ending")
'CurrentDb.Execute "UPDATE RPT0185 SET [Month_Ending] = " & MonthEnding
DoCmd.RunSQL "UPDATE RPT0185 SET [Month_Ending] = " & MonthEnding
DoCmd.SetWarnings True
MsgBox "Done"
End Sub
Thanks!
JM