@mikenyby As I mentioned in my earlier response, if you want to include more than one type of relational operator in your expressions, you almost certainly will also need to provide the ability to specify where the parentheses go. Your interface does not account for that. It looks smooth and professional to just keep adding more fields - up to three but if you want to use AND, OR, and NOT, then a better interface would be to have all the three options always visible and add spaces between each variable where parentheses can be placed. So:
RO1 - Paren - firstField - condition1 ( =, >, < , <>, Like) - value1 - paren - RO2 - paren - secondfield - condition2 - value2- paren - RO3 - paren - thirdfield - paren ---- even this is not flexible enough to cover all situations. It is basically a framework and it will intimidate many users.
There is no way YOU can decide how to bracket the expression. Only the user knows his objective as I pointed out with a very simple example.
My search form is fixed because once the application is developed, new columns do not get added frequently and not all columns are searchable anyway. Yours is also fixed but in a more limited fashion and is just as susceptible to future maintenance requirements as mine is should more fields get added. So, your solution is different but not necessarily better or worse. I considered the possibility of implementing some type of OR support but couldn't justify the complexity AND the users would be REQUIRED to understand Boolean logic in order to create successful criteria AND they would need to be able to place the parentheses correctly. Programmers have trouble with this concept. How are users going to get it right?
So, you have to figure out what is the real objective of the search form. In my case, the objective was to filter the recordset to find specific records that needed to be reviewed. Perhaps the rules for a state program were being changed and not all people who were benefiting from it would be eligible next year.
In the end, I elected to create exportable queries to solve the problems that the search form didn't solve. The users could then open the data in Excel and do additional filtering including on columns that were not included in the search form itself.
Using combos to select fields, may offer a slight coding advantage but the users actually like seeing their options laid out for them along with occasional comments regarding usage which you could do with the combo but it is harder.
And finally, if you decide to pursue this search variation, use tables for your combos, don't hard code them as Value Lists. Once you have more than a few items, Value Lists become very difficult to maintain. For example, are you prepared to embed the new option at the correct spot to maintain a logical sort order?
Although I occasionally write code to solve problems for posters, I don't write code that I would never have a need for myself. This falls into that category.
If you like your interface but are not welded to the idea of using relational operators other than AND, the code to implement your design is actually pretty simple and works pretty much like what you see in my code sample. The difference is you would abstract the code slightly and put it all in a separate procedure instead of in the click events of the controls. That way you pass in a reference to the control as you call the procedure so the code always refers to the control by the reference rather than the control name. So, the same physical code is used for all three or more combos. Obviously, the more columns you support, the more you gain from this simple method of reuse.
If you need to add code to handle different fields differently, for example, Year is in your list. You are now adding complexity because the sub is no longer a black box. It has to operate differently depending on the value passed to it. This goes against the principles of coupling and cohesion. When your "reusable" code has to take different paths depending on an input argument, it is technically no longer reusable.
Hopefully Year isn't the name of an actual field. It probably references the year of a date field so your code must know what the name of that field actually is. So, you would have to extract year by using the year function on whatever date field year refers to. This is another reason for using a table to hold the RowSource for the combo. You can display user friendly names but keep in the table the actual column name and table it comes from.
