Overly complex select query difficult to edit

[mmerig]

If memory serves, you may find the design view significantly altered next time you open the query. I always build the SQL in code with more than 2-3 optional criteria.

Your memory is correct, and I spoke too soon. The query became huge again, bigger than it was before, because I had added some more search options. It works, but it adds about a megabyte to the file size.

I thought the design view was a handy way to edit the SQL code. Before the code "blew-up", I could view it in SQL and it was short, maybe a 1/4 of a page. Why would MS Access expand such a simple query to a hugely complex one? It is so large now, it won't open in design view.

I only have one criteria for each field. The OR operator seems to have made the expansion happen.

One idea I had was to make the combo box control have a "" default value, so maybe I would not need the OR and Is Null parts of the code. But the OR is Null option seemed more logical to me, and it worked.

I will start over, make the query short, view it in SQL, and move it into code. The last step i am not sure about but will look into it.

Thanks again.

[June7]

Can use code to build filter criteria and apply to form or report without modifying query object.

Why do you need to allow uses to determine what fields are retrieved by query? I have done this only once before for export to Excel.

[mmerig]

Can use code to build filter criteria and apply to form or report without modifying query object.

Why do you need to allow uses to determine what fields are retrieved by query? I have done this only once before for export to Excel.

The database ties together images and descriptions and metadata about them.. The images are historic photographs of the landscape, and there are recent repeats (re-takes) of them to see changes over time (some > 150 years). There are hundreds of images,across a wide geographic area, different ecological zones, etc. Users may just want to see images from a particular region, and/or ecological setting. So the query's purpose is to filter the image-set for a user. Form controls (combo boxes) that modify the query make it more user friendly.

There are nine combo boxes that set the criteria in the query, and most can be used in combination to really narrow down the set, and they all can be used one at a time as well.

Perhaps if I don't open the query in design view again, the code will stay small. So far, that is the case as indicated by file size.

[June7]

I had thought the image in post 5 was from your db but now see it isn't.

You want to use modifying query object because of combining AND and OR operators? That is complicated to code for (I did it a little in the one instance I mentioned earlier). Not sure modifying query object is easier than just applying filter to form or report. It's the same WHERE structure.