The problem is a select query that provides input to a form that displays lots of information in 22 text boxes and an image box. The form has a subform, and the database is one-to-many.
The select query is written to with a search form with several combo box controls. The controls allow a multi-level search,and each control can be used alone or in combination, or any or all of them can be left blank. (If all are blank, then every record shows up in the search). There are no calculations or updates to the data, just simple selections.
The form and query worked fine, and the query was constructed via Access (via a wizard I think, it has been months so not sure) while making the search form. I see the structure in design view, but the SQL version is very long (1583 words). If I add another search control, I have to edit the criteria and there is a pattern to it that I expand on. But the number of rows for criteria expand geometrically (2, 4, 8, etc) by the number of columns, and that is getting unwieldy. There are many "Is Null" criteria along with select statements. Should I send the SQL version for someone to look at?
Maybe there is a much simpler way to make the query. After adding another combo box search level, it took a while to edit it and make it work. It totally works except for one search option in particular. I can probably fix that but I see lots of tedious criteria entries ahead if I want to add more search levels. There are six levels now, and that does not seem like a lot. I would like to add three or four more
I am new to the forum, and not an expert (obviously) with Access. If there was a way that Access could modify the query code as I add controls (for searching), that would be ideal.
Thanks.