tingler0


I am trying to import a csv file once the user has selected a date. but now i am getting this runtime error. If anyone can help please. Here is the code.

Dim db As Database
Dim fLen As Integer
Dim filepath As String
Dim FileName As String
Dim rsImport As Recordset
Dim strQry As String
Dim strSQL As String
Dim intYesNo As Variant
Dim intYesNo2 As Variant


autoYesNo = MsgBox("Automatically Process Contract Report Update ", vbYesNo)
'Prompts user to select a date for the report to be imported
Line572: SelectDate = InputBox("Enter the Date of the Report You Want to Import" & Chr(13) & Chr(10) & Chr(10) & _
"The Date should be in the Format MM/DD/YYYY", "Report Import")
'Exits procedure if the date is left blank
If IsNull(SelectDate) = True Then
' GoTo Exit_OptionLckBox_Click
Exit Sub
End If

DoCmd.TransferText acImportDelim, ("'"), "Contracts", \\Zbnawlfs002\Dept\AR-Collections\CashApplications\ContractLogReport\" & IIf(Month(SelectDate) < 10, Format(SelectDate, "mm"), Format(SelectDate, "m")) & "-" & IIf(Day(SelectDate) < 10, Format(SelectDate, "d"), Format(SelectDate, "dd")) & "-" & Format(SelectDate, "yyyy") & ".csv", True




Re: Runtime error '3625' within Access

Cringing Dragon


Runtime error is a generic error, and doesn't tell us much.

When you get the error, if there's a debug button visible, then choose it and it will take you to the Visual Basic editor with the problem line highlighted. From there, if you hit [F8], it will attempt to run that line again, and you will get a more useful error message. Alternatively, if you run the macro from the Visual Basic Editor then you'll get more detailed error messages than if you run it direct from the application.

However, I think the main problem in your code is that your path name does not have opening quotation marks. It should be like this:

DoCmd.TransferText acImportDelim, ("'"), "Contracts", "\\Zbnawlfs002\Dept\AR-Collections\CashApplications\ContractLogReport\"

In the Visual Basic Editor, was that line coloured red That's a pretty good clue that there's something wrong with it, even without running the code.

Note - I think that InputBox returns a string data type, not a date. Fortunately Format is usually clever enough to do the string/date conversion on the fly. If you wanted to play it safe you could convert it to a date first:

SelectDate = cDate(SelectDate)

Also check the iif statements you're using to do the date formatting. I don't think they'll cause errors, but they don't make much sense. The month one is reversed compared to the day one. I don't think the iif is necessary at all - if you DO want leading zeroes in the month (eg 01) then simply use Format(SelectDate, "mm"), no need for the iifs. If you do not want leading zeroes, then use Format(SelectDate, "m"), again no need for the iifs. Same for day. You can also do the whole format in one hit, like this:

Format(SelectDate, "mm-dd-yyyy")