CODEWITHSUNDEEP
sqlexcelms-accessvba
user3781528
user3781528

Reputation: 639

Syntax error in dynamic SQL string

Please help to fix the following syntax error with Like statement. The query works with = but I need to use Like to search in the AAchange field. I think the problem is here "WHERE [AAchange] LIKE '" & "%" & _ but I'm not sure how to correct this syntax. Please see the code below:

Sub ColorNewVariant()

Dim PolicyNum As Variant
Dim bFound As Boolean

Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rs As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim strSQL As String
Dim r As Range, cell As Range
Dim LastRow As Long

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Application.EnableEvents = False

Set r = ThisWorkbook.ActiveSheet.Range("G3:G" & LastRow)
For Each cell In r
        If cell.Value <> "" Then

            PolicyNum = cell.Value

            dbPath = PATH_MAIN & "\Report\MDL_IonTorrent.accdb"

            Set cnn = New ADODB.Connection ' Initialise the collection class variable

            'Connection class is equipped with a -method- Named Open
            '--4 aguments-- ConnectionString, UserID, Password, Options
            'ConnectionString formula--Key1=Value1;Key2=Value2;Key_n=Value_n;
            cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

            strSQL = "SELECT [AAchange] " & _
            "FROM [MDL_Table1] " & _
            "WHERE [AAchange] LIKE '" & "%" & _
            Replace(PolicyNum, """", """""", , , vbTextCompare) & _
            """"

            'Create the ADODB recordset object.
            Set rs = New ADODB.Recordset 'assign memory to the recordset

            'ConnectionString Open '--5 aguments--
            'Source, ActiveConnection, CursorType, LockType, Options
            rs.Open strSQL, cnn

            bFound = Not rs.EOF

            'Check if the recordset is empty.

            'Close the recordet and the connection.
            rs.Close
            cnn.Close
            'clear memory
            Set rs = Nothing
            Set cnn = Nothing
            'Enable the screen.

            If bFound Then
            'MsgBox "Record exists."

            Else
            'MsgBox "Record not found."
            'cell.Interior.ColorIndex = 8
            cell.Interior.Color = RGB(255, 217, 218)
            'cell.ClearComments
            'cell.AddComment "New Variant"
    'Fits shape around text
            'cell.Comment.Shape.TextFrame.AutoSize = True
            End If

End If
    Next cell

Application.EnableEvents = True

End Sub

Upvotes: 1

Views: 209

Answers (1)

HansUp
HansUp

Reputation: 97131

Change the quoting in your query's WHERE clause.

If you use single quotes to start and end the string value you build, you needn't bother with Replace() of double quotes within the PolicyNum value. That should make this task simpler and less confusing ...

strSQL = "SELECT [AAchange] " & _
    "FROM [MDL_Table1] " & _
    "WHERE [AAchange] LIKE '%" & PolicyNum & "'"
Debug.Print strSQL

Upvotes: 1

Related Questions