I’ve spent the weekend scouring Stack Overflow for an answer and I can’t find one that addresses my specific issue. Below is the SQL string of a simple Update query. I have ten of these separate queries. I can easily use VBA to run each query in succession, and that works great. BUT, I would prefer to run them using RunSQL so I don’t have to have 10 separate queries to manage. I took the SQL string from the Query Designer and pasted it to the following VBA:

Dim sql As String
sql = "UPDATE Comments LEFT JOIN KeysFound ON Comments.[Survey#] = KeysFound.[survey#] _
SET KeysFound.Lost = -1, KeysFound.[survey#] = [Comments].[survey#] _
WHERE (((Comments.Comment) Like " * Lost * "))"
DoCmd.RunSQL (sql)

Here’s what happens:

screenshot of sql string and error

I removed line breaks; still error. I added a semi-colon to end and still error. I thought running the simple SQL statement from the Query Designer would not error out.

The goal is to run 10 of these statements under one VBA Procedure so I don’t carry 10 separate queries. The only thing that changes in the SQL statement is the Like “Lost” The word Lost will be replaced with “Unhappy” and then replaced with “slow” and so on. Then the UPDATE statement simply updates the associated row in the joined table with a -1 in the “Yes/No” field when the word is found in the comments.

This works great using separate queries and running them in sequence with the Docmd.openquery, but that’s not the goal.