Search button code for categories in a subtable

[stevemanser]

If you need to make a search to find customers in a group (or groups) and the groups are defined in a separate table with a intermediate join table, then this piece of code is for you. The customers table here is called "accounts". You will need to create a text box called "Arguments" and a button call cmdCategorySearch and enter searches like this:

+business +active -london

Which will find any customers that are business and active but not in London.

Private Sub cmdCategorySearch_Click()
strArg1 = Me.Arguments
x = 0
strSQL = "SELECT * FROM accounts"
If Len(strArg1) > 0 Then
    strArg2 = Split(strArg1, " ")
    For Each v In strArg2
        If InStr(v, "+") Then
            v = Mid(v, 2)
            If x = 0 Then strSQL = strSQL & " WHERE ((" Else strSQL = strSQL & " And "
            strSQL = strSQL & "(accounts.id) In (SELECT accounts.id from qryAccounts_Groups WHERE groups.Group like '*" & v & "*' ) "
        Else
            v = Mid(v, 2)
            If x = 0 Then strSQL = strSQL & " WHERE ((" Else strSQL = strSQL & " And "
            strSQL = strSQL & "(accounts.id) Not In (SELECT accounts.id from qryAccounts_Groups WHERE groups.Group like '*" & v & "*' ) "
        End If
        x = x + 1
    Next
    strSQL = strSQL & "))"
End If
strSQL = strSQL & " ORDER BY accounts.account_name;"
'InputBox strSQL, , strSQL
Me.RecordSource = strSQL
End Sub

[NeoPa]

I assume from the wording of your thread that you're not looking for an answer but are simply sharing your code / solution to a problem you've encountered?