Open report where text contains (SQL question) (1 Viewer)

Futures_Bright · Oct 1, 2014

Hi all,

I'm struggling with a bit of SQL code in my VBA when opening a report.

I've defined a string in my code called strSQLQuery which is added to depending on how many criteria a user has defined on a form (all works so far where the criteria are drop down boxes). The problem is that I need to allow the users to search for a particular piece of text within a memo field and I can't find the VBA way to recreate other queries I've done within access.

Below are the bits of code related to the problem

Code:

Dim strSQLQuery As String

...

If Me.TextSearch = "" Then
    'don't add
Else
    If strSQLQuery <> "" Then
        strSQLQuery = strSQLQuery + " AND "
    End If
    strSQLQuery = strSQLQuery + "[QMS Clause].[Clause Text] =' Like %" & Me.TextSearch & "%'"
End If

I have tried a number of things (position of the quotation after the equals, % instead of *, capital "LIKE") but just can't seem to get it right. can someone please help me?

Edit: Problem solved! I didn't need the "=". New code below in case others have the same problem.

Code:

If Me.TextSearch = "" Then
    'don't add
Else
    If strSQLQuery <> "" Then
        strSQLQuery = strSQLQuery + " AND "
    End If
    strSQLQuery = strSQLQuery + "[QMS Clause].[Clause Text] Like '*" & Me.TextSearch & "*'"
End If

Open report where text contains (SQL question) (1 Viewer)

Futures_Bright

Registered User.

Similar threads

Users who are viewing this thread