Multiple, but variable amount, user defined criteria issue

[Micron]

I agree the mix and match scenario you refer to would be a bit more challenging. But at the risk of repeating myself, my interpretation based on the original post was not to mix and match but to supply one or the other possibility. To that end I said use option buttons with labels that define what the selection means: One for records where all criteria must be present, one for records where any are present. If you want to do what you said, I guess I'd put combos in between textboxes for operators and read it right to left.
Foo AND bar OR Dis And Dat


I don't recall ever doing that, but I don't see why I couldn't replace the actual operators with the combobox values (e.g. AND or OR). I just might take a whack at it for fun. If anyone has any 'heads up' things to consider, they could be useful comments.

[RobOtowski]

Just to clarify what it is that I am looking for:
The form would have four blank Text Boxes. Users enter maximum of one search term per box. User selects "search for tickets that contain ALL of the search terms" or they select "search for tickets that contain ANY of the search terms". So option 1 ("and") would be something like "search for 'proj' AND '129' AND 'fire'" and option 2 ("or") would be something like "search for 'proj' OR '129' OR 'fire'".

It is possible that down the road I may want to create a search that lets you mix ANDs and ORs so you could search something like (("proj" AND "129") OR ("fire" AND "202")), but at the moment I think that is too much for my users to understand, let alone utilize, so it's an extremely low priority. However, don't let me stop you all from discussing it! I'm still learning quite a lot from this thread and I thank you all!

[ssanfu]

Apparently I misunderstood the question.

So I agree with Micron , use an option group or a combo box to select "AND" or "OR".
I still don't like to modify/replace the record source of a form(while not in design view); I still prefer to set a filter.

It would be extremely easy to modify Allen Browne's code to be able to use an option group or a combo box to select "AND" or "OR". The code only uses (searches/filters) the controls ("Tag1", "Tag2",...) that are not blank/NULL. (The code is at http://www.allenbrowne.com/ser-62code.html)

In the header, have the four controls, an option group/combo box, and 2 buttons: "Search" & "Clear Search".

[Micron]

As I said, I thought I'd take a poke at this and came up with this design, based on the Northwind Orders table. I did some coding around date validation and putting together the button click, but didn't finish it. I got the notion I might be able to make something that could examine any table or query and build a continuous form with operators (>,=,>= etc.) suitable to the data types of the fields. The challenge, as always with continuous forms, would be how to handle the form's inability to display different operator values in the combo boxes on each row. I have an idea germinating on how to fake that, but need time to try it out.

Robotowski, if the picture looks like something you could use, PM me and let me know and I'll see what I can do to accomodate you.
To explain the image: Where Country = value AND City = value AND Customer = value AND Order Date > value AND Required Date > value OR shipper = value. Note that there is no grouping possible (this AND that) OR (dis AND dat) vs (this AND that OR dis) AND dat vs what you'd get with no brackets. As shown, it relies on the user making sensible choices wrt operators (the AND / OR choices I would call conjunctions, not operators). The version I am working on now would be more robust in that both operators and conjunctions would be combo driven.

[RobOtowski]

Micron, Sorry I'm just now seeing what you posted for some reason I stopped being notified of replies to the thread.

This looks great! But I've had to shelve that project for now as more pressing matters have arisen requiring my expertise (which is to say I'm the only one in the department that's ever used Access before...). Hopefully I can come back to this, I like this project better than what I have to work on. Thanks for your help.