Struggling with Editting a "search" query

[Kemidan2014]

So a couple months ago i created a Query, Form, and Report to allow users to Search the Maindata Table and present Records in a report given specific criteria for feilds.

everything was working fine until someone had actually asked me for a list of records by group for a criteria I had not originally provided a field for in my form. So when i went to add said feild so that i could provide a response for that request i noticed that my original query had automatically expanded with dozens of mix and match criterias.

and now when i try to put the query in Design mode, Access is freezing on me, forcing me to end task.

Originally in the query there was just 1 criteria [Forms]![Formname].[Fieldname] for like 9 fields all related to the Main data table.

I tried to compact and repair the database but it did not seem to alleviate the problem. any suggestions?

 

[Kemidan2014]

OP update:
So i was able to "unlock it" by trying to run it from the navigation pane and entering in a value to search for, it produced results, I closed the results then it allowed me back into Design view. after trying to edit it a 2nd time and saving, it froze again, once again I forced closed it.

going back to Run the query through the navigation pane I get "Query too complex" message and it doesnt run. here is an image of the query expression builder.

Like i said, before when I originally built this, it was 1 criteria per field. Some how it grew to this, and I dont know why. There some additional fields out of frame that are also "is Null" for the Form incase user does not want to select a criteria in that field.

 

[welshgasman]

Yep, I would say that is complex
All we can see is the name of the form, which is as much use as a chocolate fireguard?

I would not even want to see the SQL view. However you could try and get to that view and copy the SQL and edit it down manageble logic.

 

[Micron]

my original query had automatically expanded with dozens of mix and match criterias.

That I don't understand. All those OR options had to be there before you attempted to add one. Perhaps you were looking at sql view before that.

Access is freezing on me,

I don't think so - it's processing that monster query. While doing that it shows 'not responding' at the top, because it can't respond to actions like your mouse clicks. It does not mean there is an issue beyond being too busy for your liking.
That query looks like the sql should be constructed in vba based on form inputs. Impossible to say but it might be as simple as concatenating all the form fields that have data. If it's about all those which have no data, probably the same type of approach. If it's a mix and match then it will be more complex. The end result would be that a much shorter sql statement might be possible.

 

[Kemidan2014]

I will attempt to open SQL view, it looks like that became a monster too.

 

[Kemidan2014]

That query looks like the sql should be constructed in vba based on form inputs.

I got a feeling I may have to resort to this route. But I have never constructed a VBA SQL query (maybe not knowingly anyway...) Can you link a guide or similar post that I might learn from?
to give you an idea of the input I am asking the user to interface with for this search. I am including a snapshot of the form. all the comboboxes are Value lists with prepopulated standardized data. because the fields I am associating them with because those fields in the table will have standardized data.

 

[Micron]

I have no links handy; would have to google it so you might as well do that and find one that seems to suit your purpose. The cap words are key words in Access sql - I'm not shouting.

Some of the usual basics for a simple SELECT query:
You'd declare a variable for each of the following to hold
- the select part of the sql - SELECT . . . FROM and then that which deals with all of the table names and joins
- the WHERE part. This would be your major hurdle. Still not enough info to provide guidance on that. Likely code that retrieves filled in values and ignores the rest
- any other part such as HAVING or ORDER BY
One requirement is to determine that the strFrom variable is not empty (as in the case where a button runs this code yet there's no data in the form). You'd also have to loop over form controls and piece together the criteria. Still not enough info to advise on that. One thing I forgot to mention before is that each one of those Forms! references you have gets processed. **That's a lot of wasted processing if it is always the same form and control (I count about 86 references that I can see). The sql method can eliminate that by using variables.

**I'm assuming that Access does not figure out what a reference evaluates to and uses that when it forms the query plan. I'm assuming xx evaluations of the reference, even if it is the exact same form and control, which in your case, I doubt it is anyway.

 

[welshgasman]

Start here perhaps.

https://www.youtube.com/results?search_query=build+a+query+access+vba

And

SQL to VBA & Back Again

This is an enhanced version of a utility, by Allen Browne which makes it easy to convert a query from SQL to VBA (or vice versa)

isladogs.co.uk

build access query in vba - Google Search

 

[Kemidan2014]

Thank you all for your advice! time to dig in, one last question, My understanding is Running a VBA based SQL query will generate DATASHEET view of the data, could that be put into an already created REPORT form i had set up so that the data is printer freindly?

 

[Micron]

You would not open the query, you'd create the sql and then open the report. In that code you'd also set the report recordsource to your sql.