File Import Question - Please Help

[kebert111]

I am using the following VB Code to import a file into Access. The problem is that if we run this on Monday, the file we are trying to import in from Friday (and therefore is not Date - 1). Do you know how I would go about importing Friday's file?

DoCmd.TransferText acImportDelim, "XSP_Blocked_Shares Import Specification", "XSP_Blocked_Shares", "E:\Incoming\XSP\archive\XSP_Blocked_Shares_" & Format(Date - 1, "yyyymmdd") & ".txt", True, ""

Thanks.

 

[KernelK]

Heres the function that I use for this purpose (just pass it Date for the xDate parameter for today):

Public Function Daily_Date(xDate As Date) As Date
    If Format(xDate, "dddd") = "Monday" Then
        Daily_Date = DateAdd("d", -3, xDate)
    Else
        Daily_Date = DateAdd("d", -1, xDate)
    End If
End Function

and to include your formatting for your filename string you should use this function instead:

Public Function Daily_Date(xDate As Date) As String
    If Format(xDate, "dddd") = "Monday" Then
        Daily_Date = Format(DateAdd("d", -3, xDate), "yyyymmdd")
    Else
        Daily_Date = Format(DateAdd("d", -1, xDate), "yyyymmdd")
    End If
End Function

 

[kebert111]

Follow-up Question

I tried the below, but am getting an Run Type error '13' - Type Mismatch error on the else statement. Any suggestions?

Dim mVar As Date
 
    If Weekday(Now()) = 2 Then
        mVar = Format(Now() - 3, "yyyymmdd")
        
    Else
        mVar = Format(Now() - 1, "yyyymmdd")
        
    End If

DoCmd.TransferText acImportDelim, "XSP_Blocked_Shares Import Specification", "XSP_Blocked_Shares", "E:\Incoming\XSP\archive\XSP_Blocked_Shares_" & Format(mVar, "yyyymmdd") & ".txt", True, ""

 

[KernelK]

The Format function returns a string value, Dim your mVar as String instead of Date and it will work. Oh, and remove the format statement from your filename string concatenation. You have already formatted mVar in your IF THEN previous to this.