Updating a date/time field in MS Access using VBA

I'm trying to update a date/time field when a new file is imported through vba.

It works for the first file, but for subsequent imports I get a type mismatch error.

If I delete the 'FileDate' field and re-insert it, it works for the first time, but after I get the error again.

    Dim db As DAO.Database
    
    Dim str_table As String
    Dim str_sql As String
    
    Dim dt As Date

    str_table = "Items"

    dt = CDate(Format(FileDateFromPath(file_path), "MM/DD/YYYY"))
    
    Set db = CurrentDb()
    
    str_sql = "UPDATE [" & str_table & "] SET [FileDate] = #" & dt & "# " & _
        "WHERE [FileDate] Is Null OR [FileDate]='';"
   
    DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:=str_table, _
            FileName:=file_path, _
            HasFieldNames:=True ' or False if no headers
    
    db.Execute str_sql, dbFailOnError '<-- error here's

I've tried different formats for the date (DD/MM/YYYY, changed to MM/DD/YYYY), I've included # around the date...

Deleting the field and re-inserting it works, but only for the first time...

Is there something I'm missing?

Your dt variable is a Date, not a String. Therefore the Format you apply has no effect, and when you concat the variable, it will use your local date format.

Use Gustav's CSql() function when concatenating variables with SQL.

Also, a date column can not have the value '', so you can omit that.

str_sql = "UPDATE [" & str_table & "] SET [FileDate] = " & CSql(dt) & _
         " WHERE [FileDate] Is Null;"

should work.