Filter Filtered Results?

[WineSnob]

I have a main form with textboxes and a "search" button to filter the data and display the results in a subform. I am using code to set the record source from the values in the main form. Right now it is just using "AND". Example Vendor like "*ABC*" AND Date between datestart and dateend. This works fine. I would now like to filter the filtered results again using an OR criteria. The or could be where [notes] like "*visit*" OR like "*met*".
If i am not mistaken you cannot get a single query to use AND and OR. I just need a little push in the right direction with an idea.
I was thinking of creating a temp table and appending the initial results and then another button to query off the temp table. Any thoughts ??????
:banghead:

 

[boblarson]

You can use AND and OR together. If you have, let's say, an option group to select whether you want AND or OR to be used, you can do this:

Dim strWhere As String
Dim strAndOr As String
 
Select Case Me.OptionGroupNameHere
Case 1
   strAndOr = " AND "
Case 2
   strAndOr = " OR "
End Select
 
If Len(Me.ControlNameHere & vbNullString) > 0 Then
   strWhere = "[FieldNameHere]=" & Me.ControlNameHere & strAndOr
End If
 
If Len(Me.Control2NameHere & vbNullString) > 0 Then
  strWhere = strWhere & "[Field2NameHere]=" & Me.Control2NameHere & strAndOr
End If
 
...and so on until you get to
 
If Right(strWhere, 5) = " AND " Then
   strWhere = Left(strWhere, Len(strWhere)-5)
End If
 
If Right(strWhere, 4) = " OR " Then
   strWhere = Left(strWhere, Len(strWhere)-4)
End If
 
Me.Filter = strWhere
Me.FilterOn = True

 

[AccessMSSQL]

I think your idea to use a temp table is good. I use that in searches sometimes depending on the result I'm trying to accomplish. So yes, you should populate the temp table with your initial results and then apply your OR statements. Not sure where you learned that you can't use AND and OR in a statement. You should be able to but I guess it depends on how complex your statement is.

 

[AccessMSSQL]

With ANDs and ORs you just need to make sure your parenthesis are in the correct place:

select * from table
WHERE (Company = "abc"
and VendorID = 1
and InvoiceDate Between #1/1/2012# AND #6/30/2012)
and (NOTES like "*visit*" OR Notes Like "*met*")