Solved

Data type mismatch in criteria expression

Posted on 2013-05-22
2
718 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now