Not Enough Memory

 

Query Too Complex

Queries can become too complex in Microsoft Access database when  Access rewrites or restructures the where clause in a query.  When this rewrite occurs it is possible to prevent Access from writing the where clause by using the programming technique described below.

Here is a complex SQL query – not the where clause written in SQL view for a query used to select records based on several multi-select parameter lists.  Once Access re-writes this query it becomes too complex or the query runs out of memory.

WHERE ([m_projects].[project_ID] In (select project_Id from T_Projects) Or 0 In (select project_id from t_projects))
And
([m_project_bidders].[bidder_id] In (select bidder_id from t_bidders) Or 0 In (select bidder_id from t_bidders))
And
([m_projects].[project_type_ID] In (select project_type_Id from t_project_Types) Or 0 In (select project_type_id from t_project_types))
And
([m_project_items].[product_grp_id] In (select product_grp_id from t_prod_grps) Or 0 In (select product_grp_id from t_prod_grps))
And
([L_MFGs].[MFG_ID] In (select mfg_id from t_mfgs) Or 0 In (select mfg_id from t_mfgs))
And
([M_Projects].[Sales_Person_ID] In (select person_id from t_persons) Or 0 In (select person_id from t_persons))
And
([M_Projects].[Bid_Date]>=get_global(‘gbl_bid_Start_date’)
And
[M_Projects].[Bid_Date]<=get_global(‘gbl_bid_end_date’)); This query will run as is.  However, if you change to regular query design view then Access rewrites the SQL where clause to go into the criteria section of the query grid.  Below is just a sample of the part of the resulting too complex query and huge SQL Where clause:

complex SQL queries examples

If you try to save the query you get the Access error: Expression too Complex in Query Expression… blah blah blah’.  The query is too complex for Access to save or run now.

There are three workarounds to fix the problem ‘query too complex’ problem.  You likely don’t know about the third method and that is the purpose of this query tutorial.

The first query too complex solution is to break up the query into a series of queries with each query performing a couple of the where clause elements.

The second method is the create and run the query from visual basic – however this may not always work and you can still get the too complex error message.  This method also adds to the complexity of the database.

The last method (hack), possibly invented by Blue Claw, is to use the Choose function to prevent Access from rewriting the SQL where clause.  Here is an example of the workaround using the choose function with a compressed version of the SQL where clause from the example above:

WHERE (((Choose(1,([m_projects].[project_ID] In (select project_Id from T_Projects) Or 0 In (select project_id from t_projects)) And ([m_project_bidders].[bidder_id] In (select bidder_id from t_bidders) Or 0 In (select bidder_id from t_bidders)) And ([m_projects].[project_type_ID] In (select project_type_Id from t_project_Types) Or 0 In (select project_type_id from t_project_types)) And ([m_project_items].[product_grp_id] In (select product_grp_id from t_prod_grps) Or 0 In (select product_grp_id from t_prod_grps)) And ([L_MFGs].[MFG_ID] In (select mfg_id from t_mfgs) Or 0 In (select mfg_id from t_mfgs)) And ([M_Projects].[Sales_Person_ID] In (select person_id from t_persons) Or 0 In (select person_id from t_persons)) And ([M_Projects].[Bid_Date]>=get_global(‘gbl_bid_Start_date’) And [M_Projects].[Bid_Date]<=get_global(‘gbl_bid_end_date’))))<>False));

Note the Choose command in the query.  Also note that we have hard coded a ‘1’ so that the choose command only uses the first (and only) option.  Choose(1,mywherecode)

The great thing about using this solution to the query too complex problem is that you can view the query in regular design view and Access won’t change it all around:

complex SQL queries examples

Now you can add some additional SQL where clause elements without running into the complex SQL queries problems.

Another benefit of using the choose function in this way is that if you can program directly in SQL you’ll be able to create a complex query in 1/10th the time compared to programming the query in the standard grid design view.   And, coming back later to change or add a where clause element is simple and only takes a minute – otherwise it actually may be too complex for a programmer who is not familiar with the query.

Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365
Contact Information

Development in Microsoft Access, Microsoft SQL/Server and Azure

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016