• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 795
  • Last Modified:

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.
0
Morgs77
Asked:
Morgs77
2 Solutions
 
mbizupCommented:
Drop the ' delimiters for numeric data:

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

Open in new window


And assuming your data is stored in Access, use # to delimit dates:

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

Open in new window



EDIT:

<< If I remove LabourTimes.EmployeeID = '" & EmpID & "' AND from the criteria it works fine >>

If this is the case, then just remove the ' from around the ID, per my first suggestion here.
0
 
Dale FyeCommented:
It looks like your EmployeeID is a number, not a string, so try replacing:

WHERE LabourTimes.EmployeeID = '" & EmpID & "' AND

with

WHERE LabourTimes.EmployeeID = " & EmpID & " AND

(I removed the single quotes on either side of the EmpID value)
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now