Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2002
    Posts
    13

    Question Unanswered: Query too Complex Errors

    I have attempted to create a query which is based on another query. This query contains a lot of IIF statements, which have correct syntax. When I attempt to run the query, Access gives me a "Query too complex" error. I have tried MANY different things. PLEASE HELP ASAP!!!
    Gregorio E. Gonzalez

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Try to simplify your query. Just replace your nested IIFs with custom functions.

    Igor

  3. #3
    Join Date
    Jul 2002
    Posts
    13

    Making a custom function.

    Could you please give me an example of how to do that? I am not new to programming, but new to access. My function bases itself on other tables/queries. Does this matter?

    PLEASE HELP..

    Greg
    Gregorio E. Gonzalez

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    Select MyFunc(Age) as Age1, FName, LName From tblCustomers;



    Public Function MyFunc(Byval nAge as Integer) as Text

    if IsNull(nAge) then
    MyFunc = "Not Specified"
    elseif nAge <= 0 then
    MyFunc = "Not Valid Entry"
    elseif nAge < 30 then
    MyFunc = "Under 30"
    ...
    endif

    End Function

  5. #5
    Join Date
    Jul 2002
    Posts
    13

    Exclamation CLARIFICATION OF QUERIES

    Ok... Here's the thing... These iff statements are basing themselves on other fields in the query...

    EXAMPLE:

    (Follows is the name of the table/query and the items within it)

    TABLES:

    Products {ProdNo, ProdDescription}
    Production {ProdNo, BatchSize, Inventory, Goal}
    Forecasts {Month1, Month2,....Month12}

    QUERIES:

    MEINV{iif statements based on Products and production tables}
    NOTE: for all 12 months

    DaysOFSupplyFLAG {iif statements Based on Production, Forecasts and MEINV}
    NOTE: for all 12 months.

    DaysOFSupply{iif statements Based on DaysOFSupplyFLAG and Forecasts}
    NOTE: for all 12 months.

    Even if I split up these queries into different calculations, because each calculation is related to another, I get the same error. I have attempted to do this many ways, and I was wondering if there was a way to do this in a module.


    Please Help,
    Greg G
    Gregorio E. Gonzalez

  6. #6
    Join Date
    Nov 2001
    Posts
    336
    What problems do you actually have with

    MEINV{iif statements based on Products and production tables}

    query?

  7. #7
    Join Date
    Jul 2002
    Posts
    13
    Igelin, there are no problems with the MEINV query, but there are problems when I try to run the DaysofSupply query which is based on the MEINV query and other tables.

    When I attempt to run it, I get a query too complex error. It has been suggested that I create temporary tables, and I think that it's the way to go, but I don't know how to suppress the deletion confirmation query... Is there a way to delete all the cells in the table without having a prompt????

    Greg
    Gregorio E. Gonzalez

  8. #8
    Join Date
    Nov 2001
    Posts
    336
    You can disable the notification:

    1. Go menu bar Tools | Options
    2. Click Tab Edit/Find
    3. In right upper corner check or uncheck appropriate options.

    Have you posted DaysofSupply query on DBforums so it can be looked at?

Posting Permissions

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