CODEWITHSUNDEEP
vbadatetimems-accesssql-update
Michael
Michael

Reputation: 1

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?

Upvotes: 0

Views: 581

Answers (1)

Andre
Andre

Reputation: 27634

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.

Upvotes: 1

Related Questions