By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,698 Members | 1,282 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,698 IT Pros & Developers. It's quick & easy.

Search button code for categories in a subtable

P: 3
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.


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCategorySearch_Click()
  2. strArg1 = Me.Arguments
  3. x = 0
  4. strSQL = "SELECT * FROM accounts"
  5. If Len(strArg1) > 0 Then
  6.     strArg2 = Split(strArg1, " ")
  7.     For Each v In strArg2
  8.         If InStr(v, "+") Then
  9.             v = Mid(v, 2)
  10.             If x = 0 Then strSQL = strSQL & " WHERE ((" Else strSQL = strSQL & " And "
  11.             strSQL = strSQL & "(accounts.id) In (SELECT accounts.id from qryAccounts_Groups WHERE groups.Group like '*" & v & "*' ) "
  12.         Else
  13.             v = Mid(v, 2)
  14.             If x = 0 Then strSQL = strSQL & " WHERE ((" Else strSQL = strSQL & " And "
  15.             strSQL = strSQL & "(accounts.id) Not In (SELECT accounts.id from qryAccounts_Groups WHERE groups.Group like '*" & v & "*' ) "
  16.         End If
  17.         x = x + 1
  18.     Next
  19.     strSQL = strSQL & "))"
  20. End If
  21. strSQL = strSQL & " ORDER BY accounts.account_name;"
  22. 'InputBox strSQL, , strSQL
  23. Me.RecordSource = strSQL
  24. End Sub
May 21 '07 #1
Share this Question
Share on Google+
1 Reply

NeoPa
Expert Mod 15k+
P: 31,770
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?
Jun 2 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.