Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Data type mismatch in criteria expression

Posted on 2013-05-22
2
762 Views
Last Modified: 2013-05-30
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
Comment
Question by:Morgs77
2 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 39187938
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 39187953
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question