Using FileDateName in SET statement

[kerryokie27]

I have a scenario where I receive an Excel spreadsheet that cross-references some date I have stored in a table. I need to update the table data using the common element and insert the Time/Date stamp from the Excel files into a field in the table. I have no issues getting the Time/Date stamp:

Dim filedate As Date
Dim sSQL As String
 
If sXlx <> "" Then
 
        ' Load Inbond SS >>>
        If DCount("[Name]", "MSysObjects", "[Name] = 'xls_Inbond_staging'") = 1 Then
            DoCmd.DeleteObject acTable, "xls_Inbond_staging"
        End If
 
        DoCmd.SetWarnings False
        DoCmd.TransferSpreadsheet acLink, 8, "xls_Inbond_staging", my_path & sXlx, False
        DoCmd.SetWarnings True
 
        filedate = filedatetime(sXlx)
 
        sSQL = ""
        sSQL = sSQL & "INSERT INTO tbl_Inbond_staging        (MBOL) " ' 1
        sSQL = sSQL & "SELECT F30 " ' MBOL
        sSQL = sSQL & "FROM xls_Inbond_staging "
        sSQL = sSQL & "WHERE F30<>'MasterBillNumber' "
        sSQL = sSQL & "AND nz(F30,'')<>'';"
        Call CurrentDb.Execute(sSQL)
 
        sSQL = ""
        sSQL = sSQL & "UPDATE tbl_Inbond_staging "
        sSQL = sSQL & "SET tbl_Inbond_staging.FileDT = FileDateTime(sXlx);"
        Call CurrentDb.Execute(sSQL)
 

Embedding FileDateTime in the SET statement gives an error. I've tried several iterations of using filedate in the SET statement, but none of them will give me the value in filename. Appealing to the group for help - thank you!

[NeoPa]

KerryOkie:
Embedding FileDateTime in the SET statement gives an error.

No surprise there.

The Expression Service, that interprets and executes the SQL, has no point of reference for FileDateTime() which is available to your VBA only because you have a reference set to it (The full reference in VBA is VBA.FileSystem. FileDateTime()).

You will need to insert the literal value into your SQL string formatted in the correct way for SQL to recognise and process it correctly as a Date/Time :

sSQL = "UPDATE [tbl_Inbond_staging] " _
     & "SET    [FileDT]=#%FD#;"
sSQL = Replace(sSQL, "%FD", Format(FileDateTime(sXlx) _
                                 , "yyyy\-m\-d HH:mm:ss")

Another point I'd warn you against is using the function CurrentDb() as if it were a variable. Though it returns similar objects each time it's run they're never actually the same one and this causes lots of people great confusion. Always do something as illustrated in :

Dim dbVar As DAO.Database
 
Set dbVar = CurrentDb()
Call dbVar.Execute(...)
...
Call dbVar.Execute(...)
...
etc.