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:
If you try to save the query you get the Access error: Expression to 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:
Now you can add some additional SQL where clause elements without running into the
complex SQL queries problems.
