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

filter results based on multiple criteria

P: 80
Ok, I know I've seen how to do this somewhere but I can't seem to find it again...

My setup is I have a form that has 5 text boxes and 1 check box. "txtFirstName", "txtLastName", "txtDelDate", "txtPickupDate", "txtOrderNumber", and "chkAll". I also have a subform that lists all open order numbers, "Open Order Subform".

I'm trying to have it where I can type a last name and a delivery date and have that filter the results that show up on the sub form.

TIA
Aug 10 '07 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,392
Ok, I know I've seen how to do this somewhere but I can't seem to find it again...

My setup is I have a form that has 5 text boxes and 1 check box. "txtFirstName", "txtLastName", "txtDelDate", "txtPickupDate", "txtOrderNumber", and "chkAll". I also have a subform that lists all open order numbers, "Open Order Subform".

I'm trying to have it where I can type a last name and a delivery date and have that filter the results that show up on the sub form.

TIA
Does LastName and DeliveryDate make a unique pair?
Aug 10 '07 #2

P: 80
Hi, I was just using last name and delivery date as an example. Im not 100% sure what you mean by unique pair, but i would guess that means theres only one combination of the 2. If thats correct, then maby, but not necasarily. All I'm trying to do is filter the list of Open Orders down to a managable number. It would be like if I opend the query behind the form and typed in "Bill" in the First Name criteria, and "Johnson" in the last name criteria so I would only see the open orders that Bill Johnson has.
Aug 10 '07 #3

Rabbit
Expert Mod 10K+
P: 12,392
If you're doing it through a subform, maybe you can just set up the parent/child properties to automatically filter.

If not you can always use the Filter property of the form.
Aug 10 '07 #4

P: 80
The text boxes for the main form search criteria are unbound. So linking them together wouldn't work right?

It seems to me like it should be something like,

Expand|Select|Wrap|Line Numbers
  1. Dim search1 As String
  2. Dim search2 As String
  3. Dim search3 As String
  4. Dim search4 As String
  5.  
  6. search1=Me!txtFirstName.value
  7. search2=Me!txtLastName.value
  8. search3=Me!txtDelDate.value
  9. search4=Me!txtPickupDate.value
  10.  
  11. Me!Open_Orders_Subform.Filter = "[First Name]= " & search1
  12. Me!Open_Orders_Subform.Filter = "[Last Name]= " & search2
  13. Me!Open_Orders_Subform.Filter = "[Delivery Date]= " & search3
  14. Me!Open_Orders_Subform.Filter = "[Pickup Date]= " & search4
  15.  
  16. Me!Open_Orders_Subform.FilterOn = True
  17.  
But that isn't workin. Is it even close, or on the right track?
Aug 10 '07 #5

Rabbit
Expert Mod 10K+
P: 12,392
The text boxes for the main form search criteria are unbound. So linking them together wouldn't work right?

It seems to me like it should be something like,

Expand|Select|Wrap|Line Numbers
  1. Dim search1 As String
  2. Dim search2 As String
  3. Dim search3 As String
  4. Dim search4 As String
  5.  
  6. search1=Me!txtFirstName.value
  7. search2=Me!txtLastName.value
  8. search3=Me!txtDelDate.value
  9. search4=Me!txtPickupDate.value
  10.  
  11. Me!Open_Orders_Subform.Filter = "[First Name]= " & search1
  12. Me!Open_Orders_Subform.Filter = "[Last Name]= " & search2
  13. Me!Open_Orders_Subform.Filter = "[Delivery Date]= " & search3
  14. Me!Open_Orders_Subform.Filter = "[Pickup Date]= " & search4
  15.  
  16. Me!Open_Orders_Subform.FilterOn = True
  17.  
But that isn't workin. Is it even close, or on the right track?
Not quite. To reference the filter you should use Me.SubformName.Form.Filter and Me.SubformName.Form.FilterOn

Strings need to be surrounded by single quotes:
Expand|Select|Wrap|Line Numbers
  1. "[First Name] = '" & search1 & "'"
For dates you use pound (#) characters.

To string together multiple search criteria you need to append and use the keyword AND.

SomeString = "[First Name] = 'Bill'"
SomeString = SomeString & " AND [Last Name] = 'Stuckman'"

You'll also need to think about cases where the user leaves the field blank. So you'll probably need to throw in some If statements.
Aug 10 '07 #6

P: 80
Ok, This is how I got mine to work;

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command18_Click()
  2.  
  3. Dim searchfor As String
  4. Dim search1 As String
  5. Dim search2 As String
  6. Dim search3 As String
  7. Dim search4 As String
  8.  
  9.  If IsNull(first) = False Then
  10.        search1 = Me.first.Value
  11.        searchfor = ""
  12.        searchfor = "[First Name] = '" & search1 & "'"
  13.  End If
  14.  If IsNull(last) = False Then
  15.       search2 = Me.last.Value
  16.       searchfor = searchfor & "AND [Last Name] = '" & search2 & "'"
  17.             If IsNull(first) = True Then
  18.             searchfor = ""
  19.             searchfor = searchfor & "[Last Name] = '" & search2 & "'"
  20.             End If
  21.  End If
  22.  If IsNull(Phone) = False Then
  23.       search3 = Me.Phone.Value
  24.       searchfor = searchfor & "AND [Phone] = '" & search3 & "'"
  25.            If IsNull(last) = True Then
  26.            searchfor = ""
  27.            searchfor = searchfor & "[Phone] = '" & search3 & "'"
  28.            End If
  29.  End If
  30.  If IsNull(email) = False Then
  31.       search4 = Me.email.Value
  32.       searchfor = searchfor & "AND [email] = '" & search4 & "'"
  33.            If IsNull(Phone) = True Then
  34.            searchfor = ""
  35.            searchfor = searchfor & "[email] = '" & search4 & "'"
  36.            End If
  37.  End If
  38.  
  39.  Me.[CUST_NAME subform].Form.Filter = searchfor
  40.  Me.[CUST_NAME subform].Form.FilterOn = True
  41.  
  42. End Sub
  43.  
Thanks so much for al your help Rabbit
Aug 22 '07 #7

Rabbit
Expert Mod 10K+
P: 12,392
Not a problem. Good luck.
Aug 22 '07 #8

Post your reply

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