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

Multiple unbound text boxes - filter not working for all but works on just 1

P: 4
Hello all,

I am trying to filter a basic form with multiple unbound textboxes. Right now I have separate boxes for each criteria (which is great for what we need, however I was asked to allow for the ability to filter the db for multiple drawings...

Expand|Select|Wrap|Line Numbers
  1.     If Not IsNull(Me.filtercompany) Then
  2.         strWhere = strWhere & "([Company] = """ & Me.filtercompany & """) AND "
  3.     End If
  4.  
  5.     If Not IsNull(Me.filterangle) Then
  6.         strWhere = strWhere & "([Angle] = """ & Me.filterangle & """) AND "
  7.     End If
  8.  
  9.     If Not IsNull(Me.filtersize) Then
  10.         strWhere = strWhere & "([Size] = " & Me.filtersize & " ) AND "
  11.     End If
  12.  
  13.     If Not IsNull(Me.filterdwg) Then
  14.         strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg & "*"") AND "
  15.     End If
  16.  
  17.     If Not IsNull(Me.filtertype) Then
  18.         strWhere = strWhere & "([Type] Like ""*" & Me.filtertype & "*"") AND "
  19.     End If
  20.  
  21.     If Not IsNull(Me.filtersch) Then
  22.         strWhere = strWhere & "([SCH] Like ""*" & Me.filtersch & "*"") AND "
  23.     End If
  24.  
  25.     If Not IsNull(Me.searcha) Then
  26.         strWhere = strWhere & "([Job] >= '" & Me.searcha & "' AND [Job] <= '" & Me.searchb & "') AND "
  27.     End If
  28.  
My question is this,

I have created 3 more Unbound textboxes identified as filterdwg2, filterdwg3 and filterdwg4

I have tried to have the form filter all 4 text boxes by adding;

Expand|Select|Wrap|Line Numbers
  1.     If Not IsNull(Me.filterdwg) Then
  2.         strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg2 & "*"") AND "
  3.     End If
  4.  
  5. If Not IsNull(Me.filterdwg) Then
  6.         strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg3 & "*"") AND "
  7.     End If
  8.  
  9. If Not IsNull(Me.filterdwg) Then
  10.         strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg4 & "*"") AND "
  11.     End If
  12.  
However it seems that this ONLY works for 1 box at a time... in that if I put a number in box 1 and 2 nothing is produced, but if I only populate box 2 the results for that particular drawing are shown.

Does anyone have any advice on how i can either tailor the following line to include a search on 4 individual unbound text boxes (where the user searched for only a portion of the drawing number)

If Not IsNull(Me.filterdwg) Then
strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg & "*"") AND "
End If

Any advice is greatly appreciated.
Apr 4 '17 #1

✓ answered by jforbes

There are some typos
Expand|Select|Wrap|Line Numbers
  1. ...
  2.     If Not IsNull(Me.filterdwg) Or Not IsNull(Me.filterdwg2) Or Not IsNull(Me.filterdwg3) Or Not IsNull(Me.filterdwg4) Then 
  3.         strWhere = strWhere & "("
  4.         If Not IsNull(Me.filterdwg) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg & "*"") OR "
  5.         If Not IsNull(Me.filterdwg2) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg2 & "*"") OR "
  6.         If Not IsNull(Me.filterdwg3) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg3 & "*"") OR "
  7.         If Not IsNull(Me.filterdwg4) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg4 & "*"") OR " 
  8.         strWhere  = Left(strWhere, Len(strWhere) - 4) & ") AND "
  9.     End If
  10. ...
The lines below wrap the Drawing Filters in parentheses so that they can be interpreted correctly:
Expand|Select|Wrap|Line Numbers
  1. strWhere = strWhere & "("
  2. and
  3. strWhere  = Left(strWhere, Len((strWhere) - 4) & ") AND "
like:
Expand|Select|Wrap|Line Numbers
  1. [Company]="ABC" AND [Angle]="12" AND ([Drawing] Like "*ABC*" OR [Drawing] Like "*DEF*")
The reason the parentheses are included is to implicitly set the Order of Operations for the WHERE Clause. This might help explain it, it's for T-SQL so it may not be exact, but it should be close: Operator Precedence. So for in the previous example, if the parentheses weren't included and the WHERE Clause looked like this:
Expand|Select|Wrap|Line Numbers
  1. [Company]="ABC" AND [Angle]="12" AND [Drawing] Like "*ABC*" OR [Drawing] Like "*DEF*"[/icode]
and if the Operators were evaluated Left to Right, and if "AND" and "OR" are considered Equal, if a Drawing number of ABC was found, it would also need a Company of ABC and Angle of 12 for the result to be returned, but if a Drawing of DEF was found, it would be returned regardless of the Company or Angle.

Share this Question
Share on Google+
9 Replies


jforbes
Expert 100+
P: 1,107
I think you have a typo:
Expand|Select|Wrap|Line Numbers
  1.     If Not IsNull(Me.filterdwg2) Then
  2.         strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg2 & "*"") AND "
  3.     End If
  4.  
  5.     If Not IsNull(Me.filterdwg3) Then
  6.         strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg3 & "*"") AND "
  7.     End If
  8.  
  9.     If Not IsNull(Me.filterdwg4) Then
  10.         strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg4 & "*"") AND "
  11.     End If
Apr 4 '17 #2

P: 4
Hello jforbes thanks for the stab, its greatly appreciated...

Not quite sure where I had the error, but I pasted what you have replied with and I have the exact same result.

Each text box works individually. No issues.

how do i get them to work together?

so if there is a text entry in box filterdwg2 AND in filterdwg3... it filters the matching results for both entities...
Apr 4 '17 #3

jforbes
Expert 100+
P: 1,107
For the most part your criteria is all ANDed together, but then you are adding options for the same field. They are still being ANDed together, where they probably should be ORed together, so you could get something like this:
Expand|Select|Wrap|Line Numbers
  1. ([Drawing] Like "*a123*") AND ([Drawing] Like "*b345*")
which means you would most likely never get a result set since the drawing probably can't be two different values. You'd want something more like:
Expand|Select|Wrap|Line Numbers
  1. ([Drawing] Like "*a123*") OR ([Drawing] Like "*b345*")

I haven't tested it, so there could be an error, but I would use some code more like this:
Expand|Select|Wrap|Line Numbers
  1.     If Not IsNull(Me.filtercompany) Then strWhere = strWhere & "([Company] = """ & Me.filtercompany & """) AND "
  2.     If Not IsNull(Me.filterangle) Then strWhere = strWhere & "([Angle] = """ & Me.filterangle & """) AND "
  3.     If Not IsNull(Me.filtersize) Then strWhere = strWhere & "([Size] = " & Me.filtersize & " ) AND "
  4.     If Not IsNull(Me.filtertype) Then strWhere = strWhere & "([Type] Like ""*" & Me.filtertype & "*"") AND "
  5.     If Not IsNull(Me.filtersch) Then strWhere = strWhere & "([SCH] Like ""*" & Me.filtersch & "*"") AND "
  6.     If Not IsNull(Me.searcha) Then strWhere = strWhere & "([Job] >= '" & Me.searcha & "' AND [Job] <= '" & Me.searchb & "') AND "
  7.     If Not IsNull(Me.filterdwg) Or Not IsNull(Me.filterdwg2) Or Not IsNull(Me.filterdwg3) Or Not IsNull(Me.filterdwg4) Then
  8.         strWhere  = strWhere  & "("
  9.         If Not IsNull(Me.filterdwg) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg & "*"") OR "
  10.         If Not IsNull(Me.filterdwg2) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg2 & "*"") OR "
  11.         If Not IsNull(Me.filterdwg3) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg3 & "*"") OR "
  12.         If Not IsNull(Me.filterdwg4) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg4 & "*"") OR "
  13.         strWhere  = Left(strWhere, Len(strWhere) - 4) & ") AND "
  14.     End If
Apr 4 '17 #4

P: 4
Ooh, i think i get that logic =D thanks!! Your help is so much appreciated...

I pasted it and the last line seems to be having issues, I do believe I have covered the string length 4 in the end of my code, but even if i remove it it still has a failure. I have bolded what seems to be causing my issue here...

thanks again! Hopefully i will hear from you!

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdFilter_Click()
  5.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  6.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  7.                         we remove the trailing " AND " at the end.
  8.     '           2. The date range works like this: _
  9.                         Both dates      = only dates between (both inclusive. _
  10.                         Start date only = all dates from this one onwards; _
  11.                         End date only   = all dates up to (and including this one).
  12.     Dim strWhere As String                  'The criteria string.
  13.     Dim lngLen As Long                      'Length of the criteria string to append to.
  14.  
  15.     '***********************************************************************
  16.     'Look at each search box, and build up the criteria string from the non-blank ones.
  17.     '***********************************************************************
  18.     'Text field example. Use quotes around the value in the string.
  19.  
  20.     If Not IsNull(Me.filtercompany) Then strWhere = strWhere & "([Company] = """ & Me.filtercompany & """) AND "
  21.     If Not IsNull(Me.filterangle) Then strWhere = strWhere & "([Angle] = """ & Me.filterangle & """) AND "
  22.     If Not IsNull(Me.filtersize) Then strWhere = strWhere & "([Size] = " & Me.filtersize & " ) AND "
  23.     If Not IsNull(Me.filtertype) Then strWhere = strWhere & "([Type] Like ""*" & Me.filtertype & "*"") AND "
  24.     If Not IsNull(Me.filtersch) Then strWhere = strWhere & "([SCH] Like ""*" & Me.filtersch & "*"") AND "
  25.     If Not IsNull(Me.searcha) Then strWhere = strWhere & "([Job] >= '" & Me.searcha & "' AND [Job] <= '" & Me.searchb & "') AND "
  26.     If Not IsNull(Me.filterdwg) Or Not IsNull(Me.filterdwg2) Or Not IsNull(Me.filterdwg3) Or Not IsNull(Me.filterdwg4) Then strWhere = strWhere & "("
  27.     If Not IsNull(Me.filterdwg) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg & "*"") OR "
  28.     If Not IsNull(Me.filterdwg2) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg2 & "*"") OR "
  29.     If Not IsNull(Me.filterdwg3) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg3 & "*"") OR "
  30.     If Not IsNull(Me.filterdwg4) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg4 & "*"") OR " strWhere  = Left(strWhere, Len((strWhere) - 4) & ") AND "
  31.     End If
  32.  
  33.  
  34.     '***********************************************************************
  35.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  36.     '***********************************************************************
  37.     'See if the string has more than 5 characters (a trailing " AND ") to remove.
  38.     lngLen = Len(strWhere) - 5
  39.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  40.         MsgBox "No criteria", vbInformation, "Nothing to do."
  41.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  42.         strWhere = Left$(strWhere, lngLen)
  43.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  44.         'Debug.Print strWhere
  45.  
  46.         'Finally, apply the string as the form's Filter.
  47.     End If
  48.  
  49.         Me.Filter = strWhere
  50.         Me.FilterOn = True
  51.  
  52. End Sub
  53.  
  54. Private Sub cmdReset_Click()
  55.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  56.     Dim ctl As Control
  57.  
  58.     'Clear all the controls in the Form Header section.
  59.     For Each ctl In Me.Section(acHeader).Controls
  60.         Select Case ctl.ControlType
  61.         Case acTextBox, acComboBox
  62.             ctl.Value = Null
  63.         Case acCheckBox
  64.             ctl.Value = False
  65.         End Select
  66.     Next
  67.  
  68.     'Remove the form's filter.
  69.     Me.FilterOn = False
  70. End Sub
  71.  
  72. Private Sub Command152_Click()
  73.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  74.     Dim ctl As Control
  75.  
  76.     'Clear all the controls in the Form Header section.
  77.     For Each ctl In Me.Section(acHeader).Controls
  78.         Select Case ctl.ControlType
  79.         Case acTextBox, acComboBox
  80.             ctl.Value = Null
  81.         Case acCheckBox
  82.             ctl.Value = False
  83.         End Select
  84.     Next
  85.  
  86.     'Remove the form's filter.
  87.     Me.FilterOn = False
  88. End Sub
  89.  
  90.  
  91. Private Sub Form_BeforeInsert(Cancel As Integer)
  92.     'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
  93.     'We prevent new records by cancelling the form's BeforeInsert event instead.
  94.     'The problems are explained at http://allenbrowne.com/bug-06.html
  95.     Cancel = True
  96.     MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
  97. End Sub
  98.  
  99. Private Sub Form_Load()
  100. Me.OrderBy = "Job DESC"
  101. Me.OrderByOn = True
  102. End Sub
  103.  
  104.  
  105. Private Sub Form_Open(Cancel As Integer)
  106.     'Remove the single quote from these lines if you want to initially show no records.
  107.     Me.Filter = "(False)"
  108.     'Me.FilterOn = True
  109. End Sub
  110.  
  111.  
  112.  
  113. Private Sub print_rpt_Click()
  114.  
  115.       DoCmd.OpenReport "rpt_result", acViewReport, , Me.Filter
  116.  
  117. End Sub
  118.  
  119.  
  120.  
  121.  
Apr 4 '17 #5

NeoPa
Expert Mod 15k+
P: 31,342
Hi Ari.

You have two bold lines and one of them (#30) won't compile as you seem to have lost the new line between lines.

You also have an extraneous End If on line #31.

Use of quotes within SQL is often fraught. Try Quotes (') and Double-Quotes (") - Where and When to use them.

Removal of the extraneous AND or OR when building up such a string can more easily and reliably be achieved by having the extra one at the front. If you know which type it must be (AND or OR) then use a variation of :
Expand|Select|Wrap|Line Numbers
  1. strSQL = Mid(strSQL, 6)     ' Allow for extra " AND ".
or even the following if you aren't sure which :
Expand|Select|Wrap|Line Numbers
  1. strSQL = Trim(Mid(strSQL, 5))     ' Allow for extra " AND " or " OR ".
Please always check your posted code compiles before posting it in a thread. This wastes less of everybody's time - particularly yours. That said, you're doing a pretty good job so far of posting and responding intelligently, so please take that as advice rather than criticism.

Good luck with your project.
Apr 5 '17 #6

jforbes
Expert 100+
P: 1,107
There are some typos
Expand|Select|Wrap|Line Numbers
  1. ...
  2.     If Not IsNull(Me.filterdwg) Or Not IsNull(Me.filterdwg2) Or Not IsNull(Me.filterdwg3) Or Not IsNull(Me.filterdwg4) Then 
  3.         strWhere = strWhere & "("
  4.         If Not IsNull(Me.filterdwg) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg & "*"") OR "
  5.         If Not IsNull(Me.filterdwg2) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg2 & "*"") OR "
  6.         If Not IsNull(Me.filterdwg3) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg3 & "*"") OR "
  7.         If Not IsNull(Me.filterdwg4) Then strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg4 & "*"") OR " 
  8.         strWhere  = Left(strWhere, Len(strWhere) - 4) & ") AND "
  9.     End If
  10. ...
The lines below wrap the Drawing Filters in parentheses so that they can be interpreted correctly:
Expand|Select|Wrap|Line Numbers
  1. strWhere = strWhere & "("
  2. and
  3. strWhere  = Left(strWhere, Len((strWhere) - 4) & ") AND "
like:
Expand|Select|Wrap|Line Numbers
  1. [Company]="ABC" AND [Angle]="12" AND ([Drawing] Like "*ABC*" OR [Drawing] Like "*DEF*")
The reason the parentheses are included is to implicitly set the Order of Operations for the WHERE Clause. This might help explain it, it's for T-SQL so it may not be exact, but it should be close: Operator Precedence. So for in the previous example, if the parentheses weren't included and the WHERE Clause looked like this:
Expand|Select|Wrap|Line Numbers
  1. [Company]="ABC" AND [Angle]="12" AND [Drawing] Like "*ABC*" OR [Drawing] Like "*DEF*"[/icode]
and if the Operators were evaluated Left to Right, and if "AND" and "OR" are considered Equal, if a Drawing number of ABC was found, it would also need a Company of ABC and Angle of 12 for the result to be returned, but if a Drawing of DEF was found, it would be returned regardless of the Company or Angle.
Apr 5 '17 #7

P: 4
jforbes, cheers man! that's incredible. such a simple fix! =D thank you!
Apr 5 '17 #8

NeoPa
Expert Mod 15k+
P: 31,342
JForbes:
The reason the parentheses are included is to implicitly set the Order of Operations for the WHERE Clause.
He means explicitly, but I'm sure you understood ;-)
Apr 6 '17 #9

jforbes
Expert 100+
P: 1,107
Haha, Thanks NeoPa. I should have caught that.
*Takes another swig off the bottle*
Apr 6 '17 #10

Post your reply

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