Thread: Problem with string and date variables.

Hello everyone.

I am trying to pass a date (short date entered into a textbox) into a string expression that is used to create a file path. What's at issue is the following piece of code:

strFullFileName = strFilePrefix & Format(asofDATE, "yyyymmdd") & ".xls"

The variable strFullFileName reads empty. So basically it shows up as

strFullFileName = " "

in the immediate window. Now everything else: strFilePrefix and Format(asofDATE, "yyyymmdd") is fine, that is acccording to the immediate window they have the correct values.

Below is a larger snapshot of the project. Any suggestions?

Private Sub cmdReturnMTMPortfolio_Click()
    Dim appXL As Excel.Application
    Dim strFilePath As String
    Dim strFileName As String
    Dim strFilePrefix As String
    Dim strFullFileName As String
    Dim strFile As String
    Dim asofDATE As Date
    Dim strasofDATE As String
    Dim txtDate As TextBox
    Dim wbk As Excel.Workbook
            
    On Error GoTo HandleErr
    strFilePrefix = "mtm_report_asof_"
    strFilePath = "N:\CBI-DEPT\Application Delivery\Assignments and Projects\FCIP Program\Spreadsheet Automation\Product\Excel Automation\Robin\Communication\Portfolio List\"
       
    asofDATE = Me.txtDate.Value
   
    Set appXL = New Excel.Application
    
    strFullFileName = strFilePrefix & Format(asofDATE, "yyyymmdd") & ".xls"
    
    Set wbk = appXL.Workbooks.Open(strFilePath & strFullFileName)
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tempMTMReportData", strFilePath & strFullFileName, True, "MTM Detail!"
    DoCmd.OpenQuery "qryCPNamesForMTMDailyReportParentandChild"