Run time error '13' Type mismatch (1 Viewer)

The following code:

Private Sub Archive_Click()
'Run Archive - Append and Delete.

Dim strSQLAppend As String
Dim strSQLDelete As String
Dim errLoop As Error


'Define two SQL statements for action queries.

strSQLAppend = "INSERT INTO tblHelpdeskcallsArchive " & _
"( ID, Date Opened, Priority, User Name, User Name1, Department, " & _
"Contact Number, Call Description, Allocated To, Email, " & _
"Date Closed, Days Open, Additional Coments, Call Resolution, " & _
"Category, Organisation, Route received ) " & _
"SELECT tblHelpdeskcalls.ID, " & _
"tblHelpdeskcalls.Date Opened, " & _
"tblHelpdeskcalls.Priority, " & _
"tblHelpdeskcalls.User Name, " & _
"tblHelpdeskcalls.User Name1, " & _
"tblHelpdeskcalls.Department, " & _
"tblHelpdeskcalls.Contact Number, " & _
"tblHelpdeskcalls.Call Description, " & _
"tblHelpdeskcalls.Allocated To, " & _
"tblHelpdeskcalls.Email, " & _
"tblHelpdeskcalls.Date Closed" & _
"tblHelpdeskcalls.Days Open" & _
"tblHelpdeskcalls.Additional Coments" & _
"tblHelpdeskcalls.Call Resolution" & _
"tblHelpdeskcalls.Category" & _
"tblHelpdeskcalls.Organisation" & _
"tblHelpdeskcalls.Route received" & _
"FROM tblHelpdeskcalls " & _
"WHERE tblHelpdeskcalls.Date Closed < #" & ArchiveDate & "#;"

' strSQLDelete = "DELETE tblHelpdeskcalls.ID, " & _
' "tblHelpdeskcalls.Date Opened, " & _
' "tblHelpdeskcalls.Priority, " & _
' "tblHelpdeskcalls.User Name, " & _
' "tblHelpdeskcalls.User Name1, " & _
' "tblHelpdeskcalls.Department, " & _
' "tblHelpdeskcalls.Contract Number, " & _
' "tblHelpdeskcalls.Call Description, " & _
' "tblHelpdeskcalls.Allocated To, " & _
' "tblHelpdeskcalls.Email, " & _
' "tblHelpdeskcalls.Date Closed, " & _
' "tblHelpdeskcalls.Days Open, " & _
' "tblHelpdeskcalls.Additional Coments, " & _
' "tblHelpdeskcalls.Call Resolution, " & _
' "tblHelpdeskcalls.Category, " & _
' "tblHelpdeskcalls.Organisation, " & _
' "tblHelpdeskcalls.Route received, " & _
' "FROM tblHelpdeskcalls " & _
' "WHERE tblHelpdeskcalls.Date Closed < #" & ArchiveDate & "#;"

'Run action query to restore data. Trap for errors,
' checking the Errors collection if necessary.


On Error GoTo Err_Execute
CurrentDb.Execute strSQLAppend, dbFailOnError
' CurrentDb.Execute strSQLDelete, dbFailOnError
On Error GoTo 0

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

End Sub


I get error run time error '13' Type mismatch, I was wondering If the fields such as Date Opened are causing the problem as they have a space in their field name?

The Type Mismatch is probably due to you using DAO when your Access is set up for ADO. Search on references - there's an FAQ in the FAQ Forum. Also, you don't need to have these queries in VBA (in fact you are making your database big without realising it) - These queries are static in structure, you should just make them in the query editor, save them as queries, and then use DoCmd.OpenQuery to execute them.