Simple command Appending Excel Data to MS Access. "Data type mismatch in criteria expression"

Question

I keep getting this error:

Run-time error '3464': Data type mismatch in criteria expression

I know this is a common error to get, but in my case none of the questions in the forum seems applicable.

This is my code, and I have checked several times that the field names are the same in Excel and Access. Could it be that one of my fields in the Access table is a check box? I'm using True/False as values in Excel for that field.

Private Sub cmdImportMaintJobs_Click()

  xlsMaintFile = OpenFileDialog(CurrentProject.Path & "\", "*.xls?", "MS Excel sheet", "*.xls; *.xlsx", "Select Excel file with Maintenance Jobs data")

  If Len(xlsMaintFile) > 0 Then
    CurrentDb.Execute "INSERT INTO tblMaintJob SELECT * FROM [Maint Jobs$A1:I50000] IN '" & xlsMaintFile & _
      "' [Excel 5.0;HDR=YES;IMEX=1] WHERE MoldID <> 0 "
    MsgBox "All done!", vbInformation, "Information"

  End If

End Sub

Try using hard coded value in the insert statement to start with the testing. — Alex
Welcome to Stack Overflow! Be sure to check out the tour as well as How to Ask and minimal reproducible example. (Also it's easier to read code in code blocks ... :-) — ashleedawg

Sergey S. Sergey S. · Accepted Answer · 2017-12-06T05:23:27

I would highly recommend to import the data from Excel into temporary table first, all fields should be text. Then you'll be able to copy imported data to working table with explicit conversion and validation. Otherwise you'll run into similar problems with other data files

Simple command Appending Excel Data to MS Access. "Data type mismatch in criteria expression"

1 Answers