filter results based on multiple criteria

[Scotter]

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

[Rabbit]

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?

[Scotter]

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.

[Rabbit]

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.

[Scotter]

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,

Dim search1 As String
Dim search2 As String
Dim search3 As String
Dim search4 As String
 
search1=Me!txtFirstName.value
search2=Me!txtLastName.value
search3=Me!txtDelDate.value
search4=Me!txtPickupDate.value
 
Me!Open_Orders_Subform.Filter = "[First Name]= " & search1
Me!Open_Orders_Subform.Filter = "[Last Name]= " & search2
Me!Open_Orders_Subform.Filter = "[Delivery Date]= " & search3
Me!Open_Orders_Subform.Filter = "[Pickup Date]= " & search4
 
Me!Open_Orders_Subform.FilterOn = True
 

But that isn't workin. Is it even close, or on the right track?

[Rabbit]

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,

Dim search1 As String
Dim search2 As String
Dim search3 As String
Dim search4 As String
 
search1=Me!txtFirstName.value
search2=Me!txtLastName.value
search3=Me!txtDelDate.value
search4=Me!txtPickupDate.value
 
Me!Open_Orders_Subform.Filter = "[First Name]= " & search1
Me!Open_Orders_Subform.Filter = "[Last Name]= " & search2
Me!Open_Orders_Subform.Filter = "[Delivery Date]= " & search3
Me!Open_Orders_Subform.Filter = "[Pickup Date]= " & search4
 
Me!Open_Orders_Subform.FilterOn = True
 

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:

"[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.

[Scotter]

Ok, This is how I got mine to work;

Private Sub Command18_Click()
 
Dim searchfor As String
Dim search1 As String
Dim search2 As String
Dim search3 As String
Dim search4 As String
 
 If IsNull(first) = False Then
       search1 = Me.first.Value
       searchfor = ""
       searchfor = "[First Name] = '" & search1 & "'"
 End If
 If IsNull(last) = False Then
      search2 = Me.last.Value
      searchfor = searchfor & "AND [Last Name] = '" & search2 & "'"
            If IsNull(first) = True Then
            searchfor = ""
            searchfor = searchfor & "[Last Name] = '" & search2 & "'"
            End If
 End If
 If IsNull(Phone) = False Then
      search3 = Me.Phone.Value
      searchfor = searchfor & "AND [Phone] = '" & search3 & "'"
           If IsNull(last) = True Then
           searchfor = ""
           searchfor = searchfor & "[Phone] = '" & search3 & "'"
           End If
 End If
 If IsNull(email) = False Then
      search4 = Me.email.Value
      searchfor = searchfor & "AND [email] = '" & search4 & "'"
           If IsNull(Phone) = True Then
           searchfor = ""
           searchfor = searchfor & "[email] = '" & search4 & "'"
           End If
 End If
 
 Me.[CUST_NAME subform].Form.Filter = searchfor
 Me.[CUST_NAME subform].Form.FilterOn = True
 
End Sub
 

Thanks so much for al your help Rabbit

[Rabbit]

Not a problem. Good luck.