Run-time error '-2147217913 (80040e07)': Data type mismatch in criteria expression. (1 Viewer)

jpindi

Registered User.
Local time
Today, 12:24
Joined
Jan 30, 2009
Messages
22
Hello I am trying to create a VBA script which removes duplicates from a single table. It searches for duplicates based on two fields (IDtable1 and IDtable2) puts them in a string and then if the string is a duplicate to the one preceeding then it deletes that line.

That part is ok, but there seems to be an overflow problem. The table is 'overflowing' on the ID (autonumber) of the table which should be straightforward enough. Debugging it highlights the line id1 = rstJunction("ID").Value. I've set id1 as an Integer which I thought would match up nicely, but notice the ID numbers are quite high (in excess of 79,000) and as a result there is an overflow error.

I then tried setting id1 as either Long, Single or Double and the error that I get is the following:
Run-time error '-2147217913 (80040e07)': Data type mismatch in criteria expression. The debugging highlight goes over the line cn1.Execute SQLd

Perhaps something with my SQL command?

Here is the code, minus the open recordset such-and-such:

Dim strDuplicate1 As String
Dim strDuplicate2 As String
Dim id1 As Long
Dim SQLd As String
strDuplicate1 = ""
strDuplicate2 = ""
id1 = 0
If rstJunction.BOF And rstJunction.EOF Then
MsgBox "No matches found"
Else
rstJunction.MoveFirst
rstJunction.MoveNext
id1 = rstJunction("ID").Value
Do Until rstJunction.EOF
strDuplicate1 = rstJunction("IDtable1").Value & rstJunction("IDtable2").Value
If strDuplicate1 = strDuplicate2 Then
SQLd = "DELETE FROM tblJunction WHERE ID = ('" & id1 & "')"
cn1.Execute (SQLd)
Else
strDuplicate2 = rstJunction("IDtable1").Value & rstJunction("IDtable2").Value
End If
id1 = 0
rstJunction.MoveNext
Loop
End If

 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:24
Joined
Aug 30, 2003
Messages
36,118
If ID is numeric, you wouldn't want the single quotes around it.
 

jpindi

Registered User.
Local time
Today, 12:24
Joined
Jan 30, 2009
Messages
22
Thanks, that worked well. It was one of those things that I knew would be a quick fix, but I just couldn't think of it. Another part of SQL code that isn't working well, though is trying to get my table to sort on a field called "IDtable2". It doesn't give an error message...it just doesn't sort. If anyone has the time, any suggestions as to what could be wrong with this?

strSQLj = "SELECT tblJunction.IDtable2 FROM tblJunction ORDER BY tblJunction.IDtable2;"
cn1.Execute (strSQLj)

The cn1 (connection) works ok because I've got a pile of code beforehand that uses it without any issues, and if it didn't, there would definitely be an error message. The IDtable2 field is a number, if that means anything (not an autonumber nor text).
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:24
Joined
Aug 30, 2003
Messages
36,118
What is it you expect to happen? You can't (shouldn't?) try to force a sort in the table itself. You sort the records when you pull them out to use them, in queries, forms, reports, recordsets, etc.
 

boblarson

Smeghead
Local time
Today, 12:24
Joined
Jan 12, 2001
Messages
32,059
And the Execute method of the connection is good for ACTION queries ONLY (Update, Append, Delete) and not for SELECT queries.
 

jpindi

Registered User.
Local time
Today, 12:24
Joined
Jan 30, 2009
Messages
22
Thanks for getting back to me, guys. I've tried different code which avoids the sorting altogether and instead loops through the table comparing each record to one another. Incidentally, how would you sort a table via VBA if the Execute method is not good?
 

Thinh

Registered User.
Local time
Today, 12:24
Joined
Dec 20, 2006
Messages
114
I don't understand your approach. the easiest approach is to use a group by query with a count. group it by the id and count it on the id aswell. if the count on the id is greater than 1 then thats a duplicate. create a query that show all the count greater than 1 then use a recordset to delete the first record if you dont care which one gets deleted.
 

jpindi

Registered User.
Local time
Today, 12:24
Joined
Jan 30, 2009
Messages
22
Sorry - there's no query, just a table. How would I do that in VBA?
 

Thinh

Registered User.
Local time
Today, 12:24
Joined
Dec 20, 2006
Messages
114
Create a query with following criteria, you have to modified to fit your table structure.
SELECT ID, Count(ID) AS CountOfID
FROM Product
GROUP BY ID
HAVING Count(ID)>1;

this will give you all duplicate records. use this in conjuction with recordset to accomplish what you need to do.
 

jpindi

Registered User.
Local time
Today, 12:24
Joined
Jan 30, 2009
Messages
22
Actually never mind, all is good. Thanks again for your help.
 

Moses76

Registered User.
Local time
Today, 15:24
Joined
May 25, 2011
Messages
49
And the Execute method of the connection is good for ACTION queries ONLY (Update, Append, Delete) and not for SELECT queries.

Hi Bob,
I am getting the same error. Trying to populate a list box using a query based record set. This is the code I have . Can you please help.

Private Sub cal1_Click()
On Error GoTo UserForm_Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim calval As Date
calval = Me.cal1.Value
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\new.mdb"
rst.Open "SELECT Name FROM Table1 WHERE DateTaken = " & calval & ";", cnn, adOpenStatic
rst.MoveFirst
With Me.List18
.RowSource = ""
Do
.AddItem rst!Name
rst.MoveNext
Loop Until rst.EOF
End With
 

Moses76

Registered User.
Local time
Today, 15:24
Joined
May 25, 2011
Messages
49
calval is not a numeric variable and so will need single quotes around it.
.


Thanks for your help . I worked with single quotes after I changed the datatype for DateTaken to text from Date and Time. Any ideas how I can keep it at date and time and still have it work.

Thanks again.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:24
Joined
Aug 30, 2003
Messages
36,118
If that's actually a date/time field try:

rst.Open "SELECT Name FROM Table1 WHERE DateTaken = #" & calval & "#;", cnn, adOpenStatic

I'm curious why you bother with the loop and AddItem when you could just use a row source type of table/query and set the row source to that SQL.
 

Moses76

Registered User.
Local time
Today, 15:24
Joined
May 25, 2011
Messages
49
I am really not sure how I can do that . Can you please educate me.

please and thanks.

Mehul
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:24
Joined
Aug 30, 2003
Messages
36,118
Me.List18.RowSource = "SELECT..."
 

Moses76

Registered User.
Local time
Today, 15:24
Joined
May 25, 2011
Messages
49
Private Sub cal1_Click()
Me.List18.RowSource = "Select * from Table1"
End Sub

I put this in there and it shows (Select * from Table1 ) in the list ....lol. I am not sure what I am doing right.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:24
Joined
Aug 30, 2003
Messages
36,118
Did you change the row source type to table/query?
 

Moses76

Registered User.
Local time
Today, 15:24
Joined
May 25, 2011
Messages
49
Yes I did .

Thanks for your reply once again.
 

Users who are viewing this thread

Top Bottom