Search code examples
sqlvbarecordsetmismatch

vba access recordset field inside SQL where clause

I have a DAO recordset that loops through a recordset and runs a SQL statement on another table, updating records of that table. For some reason, it doesn't like using the recordset value in the where clause of the SQL statement. I've tried everything I can think of.

strSQL = "Select A, B FROM TABLE1"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.BOF and Not rs.EOF Then
 rs.MoveFirst
 While (Not rs.EOF)
  DoCmd.RunSQL "UPDATE TABLE2 SET TABLE2.B = rs!B WHERE TABLE2.A = rs!A;" 
  rs.MoveNext
 Wend
End If
rs.Close

The only trouble is the where clause. It has no problem with the set clause, even though it's accessing the value the same way. I get a datatype mismatch error on the where clause.

Solution

  • Try it like this:

    "UPDATE TABLE2 SET TABLE2.B = " & rs!B & " WHERE TABLE2.A = " & rs!A

    And if they are strings, then you need to put them in single quotes like this:

    "UPDATE TABLE2 SET TABLE2.B = '" & rs!B & "' WHERE TABLE2.A = '" & rs!A & "'"