Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: General Question

    I have a question about the error "Query is too complex". What part of the query is too complex? Is the the number of fields labeled or is it the length of the SQL statement? Here is an example of one of my functions.

    CountSet: IIf([1]="1" And [3] Is Null And [2] Between #8/1/2003# And #8/31/2003#,1,IIf([1]="1" And [3] Is Null And [2] Between #9/1/2003# And #9/30/2003#,2,IIf([1]="1" And [3] Is Null And [2] Between #10/1/2003# And #10/31/2003#,3,IIf([1]="1" And [3] Is Null And [2] Between #11/1/2003# And #11/30/2003#,4,IIf([1]="1" And [3] Is Null And [2] Between #12/1/2003# And #12/31/2003#,5,IIf([1]="1" And [3] Is Null And [2] Between #1/1/2004# And #1/31/2004#,6,IIf([1]="1" And [3] Is Null And [2] Between #2/1/2004# And #2/29/2004#,7,IIf([1]="1" And [3] Is Null And [2] Between #3/1/2004# And #3/31/2004#,8,IIf([1]="1" And [3] Is Null And [2] Between #4/1/2004# And #4/30/2004#,9,IIf([1]="1" And [3] Is Null And [2] Between #5/1/2004# And #5/31/2004#,10,IIf([1]="1" And [3] Is Null And [2] Between #6/1/2004# And #6/30/2004#,11,IIf([1]="1" And [3] Is Null And [2] Between #7/1/2004# And #7/31/2004#,12))))))))))))

    I have about 18 of these in this query. Is Is there a way that I can shorten this or am I up a creek?

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I have been through this problem myself. The number of fields in the table can also be a contributor. Another contributor is if the calculated fields in the query are feeding off other calculated fields. Another contributor is if the query is based on more than one table. If the data base is very large, that is heaps of table and forms etc that can be a problem. I have one very large table that has about 250 fields so its right at maximum. At different times I need to change some of the fields from Number or Date type to text. If do this in the main data base I get Too Many Fields Defined. However, if I make a separate data base and export the table to that data base, then I can make the changes to the fields in that little data base and then export the table back to the main data base.

    The solution is to break things into smaller parts by using different queries and with each query doing some of the calculations. If this query is related to the Report you are referring to on your other thread then breaking things into different queries might go hand in hand with my comments on using different forms on your other thread.

    However if the table or tables are too big then you might also need to do it off separate tables. I have to do this on one of mine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •