1

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
11
  • In what way is your code not working? I'd suggest to start with you are missing a closing quote ' after your Replace function. Commented Dec 3, 2015 at 15:53
  • I get a run-time error at `[AAchange] LIKE '" Commented Dec 3, 2015 at 15:55
  • please be specific where to put this missing quote, there are just too many and it's very confusing. Thanks Commented Dec 3, 2015 at 15:56
  • What is the error and what are you trying to achieve? Commented Dec 3, 2015 at 15:56
  • 1
    @Jeeped Access expects ANSI wild cards with queries run from ADO/OleDb: % and _ instead of * and ? Commented Dec 3, 2015 at 16:05

1 Answer 1

1

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
Sign up to request clarification or add additional context in comments.

5 Comments

If there is still a problem, show us the output from Debug.Print strSQL and the full text of the error message. Good luck.
I don't need vbTextCompare?
You were using vbTextCompare as an argument in Replace(). If you don't use Replace(), you don't need vbTextCompare. Or have I misunderstood what you're asking?
it is strange it's finding some of the records but not all. I opened Access so I know that they exists...but they are colored red in Excel. Thanks
What happens if you take that SQL text, paste it into SQL View of a new query in the Access query designer, change LIKE to ALIKE and run it? Does Access show you the rows you expect?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.