Frenchy646
11-05-2010, 04:28 AM
Hello wonderful people,
So, I've been fiddling around with a query I was doing in VBA, as I had made a few mistakes ( for some unknown reason to me I put commas instead of "AND" for multiple criteria after the WHERE clause ), but I have now encountered an error I have no idea how to fix.
The error I get is Run-time error 3421, data type conversion error.
My first guess is, I've probably got the wrong end of the stick with regards to DAO and ADO.
If you could help me find out where I've gone wrong, and explain to me why, it would be much appreciated.
Dim sqlQuery As String
Dim myDatabase As New ADODB.Recordset
sqlQuery = "SELECT * FROM [Customer_Identifiers] WHERE ([SVC] = '" & myRecordset.Fields(0) & _
"' AND [Field1]='" & myRecordset.Fields(1) & _
"' AND [Field2]='" & myRecordset.Fields(2) & _
"' AND [Description]='" & myRecordset.Fields(3) & _
"' AND [Field3]='" & myRecordset.Fields(4) & _
"' AND [Field4]='" & myRecordset.Fields(5) & _
"' AND [Field5]='" & myRecordset.Fields(6) & _
"' AND [Field6]='" & myRecordset.Fields(7) & _
"' AND [Field7]='" & myRecordset.Fields(8) & _
"' AND [Field8]='" & myRecordset.Fields(9) & "')"
Set myDataBase = CurrentDb.OpenRecordset(sqlQuery2, CurrentProject.Connection, adOpenStatic)
So, I've been fiddling around with a query I was doing in VBA, as I had made a few mistakes ( for some unknown reason to me I put commas instead of "AND" for multiple criteria after the WHERE clause ), but I have now encountered an error I have no idea how to fix.
The error I get is Run-time error 3421, data type conversion error.
My first guess is, I've probably got the wrong end of the stick with regards to DAO and ADO.
If you could help me find out where I've gone wrong, and explain to me why, it would be much appreciated.
Dim sqlQuery As String
Dim myDatabase As New ADODB.Recordset
sqlQuery = "SELECT * FROM [Customer_Identifiers] WHERE ([SVC] = '" & myRecordset.Fields(0) & _
"' AND [Field1]='" & myRecordset.Fields(1) & _
"' AND [Field2]='" & myRecordset.Fields(2) & _
"' AND [Description]='" & myRecordset.Fields(3) & _
"' AND [Field3]='" & myRecordset.Fields(4) & _
"' AND [Field4]='" & myRecordset.Fields(5) & _
"' AND [Field5]='" & myRecordset.Fields(6) & _
"' AND [Field6]='" & myRecordset.Fields(7) & _
"' AND [Field7]='" & myRecordset.Fields(8) & _
"' AND [Field8]='" & myRecordset.Fields(9) & "')"
Set myDataBase = CurrentDb.OpenRecordset(sqlQuery2, CurrentProject.Connection, adOpenStatic)