Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
When I run the following SQL I get the error 'Data type mismatch in criteria expression'.
Function RunSQL(sql_cmd, database) Dim rs If ENABLE_SQL_LOGGING = 1 Then Dim myfile Set myfile = file.Open(SQL_LOG_FILE, openReadWrite) ' open file to append myfile.Write strEmpName & " - " & Now & " - " & sql_cmd & vbCrLf ' write data myfile.Close ' close file Set myfile = Nothing ' delete object End If If database = "SQL" Then Set rs = CreateObject("ADODB.Recordset") rs.Open sql_cmd, dbSQLConnection, 2, 3 Set RunSQL = rs ElseIf database = "Access" Then Set rs = CreateObject("ADODB.Recordset") rs.Open sql_cmd, dbAccessConnection, 2, 3 Set RunSQL = rs End If End Function
The LabourTimes.Employee field is a Long Integer and EmpID is set to 002001. To be sure, I have tried CLng(EmpID) but still get the same error.
Set rs = RunSQL ("UPDATE LabourTimes SET LabourTimes.StopDate = '" & StartTime & "' WHERE LabourTimes.EmployeeID = '" & EmpID & "' AND IsNull(LabourTimes.StopDate);", "Access")
strSql = "INSERT INTO LabourTimes (EmployeeID, WIPJob, Operation, WorkCentre, StartDate, ChargeRate) VALUES('" & EmpID & "','" & WIPJob & "','" & Operation & "','" & WorkCentre & "','" & StartTime & "','" & ChargeRate & "')" Set rs = RunSQL(strSql, "Access")
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.