Access 2010 "Query To Complex" error

Question

I have several databases with queries that work without problem in Access 2003 or Access 2007 but will not open in Access 2010.  I get a "Query To Complex" error.  These queries are not that complex and do not have an excessive number of fields.  The only fix I can find is to split the query but this will involve many hours of work and testing.   Is there a registry setting in Office or Access 2010 that is different that 2007 and prior?  If so, what is it and can it be changed?  Our Access 2010 conversion is at a standstill until this bug is fixed.  HELP!!!!!!!!!!!!!!

---

Jim

Answer 1

"Too complex" is Access-speak for "can't figure out how to do this." There are many possible reasons, such as:

• problem with data types (inappropriate operations)
• problem with Name Autocorrect (misidentification)
• reserved words (as field/table/parameter/alias names) making the query hard to interpret.

Post the SQL statement.

Tell us about the fields used in criteria, sorting, joins.

If the query is based on a another query, post the lower one too.

--

Allen Browne - Microsoft MVP.  Perth, Western Australia

Tips for Access users - http://allenbrowne.com/tips.html

Reply to group, rather than allenbrowne at mvps dot org.

"JimGriggs" wrote in message news:*** Email address is removed for privacy ***...

I have several databases with queries that work without problem in Access 2003 or Access 2007 but will not open in Access 2010.  I get a "Query To Complex" error.  These queries are not that complex and do not have an excessive number of fields.  The only fix I can find is to split the query but this will involve many hours of work and testing.   Is there a registry setting in Office or Access 2010 that is different that 2007 and prior?  If so, what is it and can it be changed?  Our Access 2010 conversion is at a standstill until this bug is fixed.  HELP!!!!!!!!!!!!!!

---

Jim

---

Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to forum, rather than allenbrowne at mvps dot org.

Answer 2

I have installed SP2 for Access 2007, because i thought that perhaps it would be possible to add more than 3 conditional formats to a control (wich is possible in Access 2010).

This is not the case for Access 2007, but once SP2 is installed you will get the same errors in your queries as in Access 2010.

So becareful with installing SP2, because i have problems with re-installing office 2007

Answer 3

Yes.  not the problem.  We are almost sure it is the total number of feilds or the amount of memory allocated to the process.  Because it works in 2007 but not 2010 we think it is a change in some registry setting.   Any idea?

---

Jim

Answer 4

Jim,

Have you reviewed the Reserved Word list (one of Allen's suggestions)?  Access 2010 is less tolerant of using them...

http://allenbrowne.com/AppIssueBadWord.html

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

Answer 5

The problem appears to be the number of fields not any SQL statement.   The query opens and works in Access 2007 but when I open the same database in Access 2010, I get the error message and the query will not open.   If I start removing fields in the query at some point the query will work without error except that the query is not fully functional as it was in 2007.   The SQL statements are not complex (if statements or simple math operations, etc.).   I had the same error message once in 2007 when I had to create a special export to excel query for a client which really did have too many fields in the query.  When I removed a couple of fields it worked.  There appears to be a max number of fields allowed in a query that has been reduced in 2010.    That is why I went looking for a registry setting but could not find one.

---

Jim