Link to home
Create AccountLog in
Avatar of Morgs77
Morgs77

asked on

Data type mismatch in criteria expression

I am trying to write VBScript in an external application that updates a record in an Access database using an SQL update query. I am using the following:

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

Open in new window

When I run the following SQL I get the error 'Data type mismatch in criteria expression'.

Set rs = RunSQL ("UPDATE LabourTimes SET LabourTimes.StopDate = '" & StartTime & "' WHERE LabourTimes.EmployeeID = '" & EmpID & "' AND IsNull(LabourTimes.StopDate);", "Access")

Open in new window

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.

If I remove LabourTimes.EmployeeID = '" & EmpID & "' AND from the criteria it works fine, so I am sure it is something to do with this.

I use the following elswhere in the script and it works fine.

strSql = "INSERT INTO LabourTimes (EmployeeID, WIPJob, Operation, WorkCentre, StartDate, ChargeRate) VALUES('" & EmpID & "','" & WIPJob & "','" & Operation & "','" & WorkCentre & "','" & StartTime & "','" & ChargeRate & "')"

Set rs = RunSQL(strSql, "Access")  

Open in new window


I can not work out what the problem is. Please help.
SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account