Updating a date/time field in MS Access using VBA

Question

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?

Andre · Accepted Answer · 2023-02-08 17:28:52Z

1

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.

answered Feb 8, 2023 at 17:28

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Over a year ago

Thanks, makes sense, but still having problems. I think it must be a problem/bug with the table, as if i delete the 'FileDate' field, and put it back in, it works for the first time, but then the type miss-match error returns. I will try a new table, or if i can't get that to work, use a temporary table when importing the data.

Collectives™ on Stack Overflow

Updating a date/time field in MS Access using VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related