3071 - Expression too complex error (1 Viewer)

[aka_BigRed]

I have a query that gives me the "3071 - Expression too complex to be evaluated.." error. The problem is I can get the EXACT same SQL to run and not produce the error. The query is part of a reporting tool that hundreds of users run (each on their own instance or a few people sharing one instance).

Here's the flow of what I do:

1 - Open DB
2 - run query
3 - Query dies w/ "3071 - Expression too complex..." error
4 - open query in SQL edit mode
5 - Rerun query and it works fine.
6 - Scratch head and curse loudly....

After modifying the query it works for a random amount of time and users don't complain. Then all of the sudden, it starts failing again, and I repeat the query edit, resave and it works fine until the next time. The problem is I never know what may or may not fix it because it will work fine after opening in edit mode. I've tried compact/repair and that has no effect on the query. The only thing that fixes it is opening it in design mode.

I need to figure out what is the root cause of this. I'm getting sick of fixing it with my edit/resave hack. It's not excessively complex or anything.

Here's my SQL. It uses some form value references in the criteria, but the form is open and values exist when I'm trying to run the query, so that shouldn't be it.

SELECT [VARIANCE DIVISION].MONTHNBR,
       CHOOSE([MONTHNBR],"January","February","March","April",
              "May","June","July","August","September",
              "October","November","December") AS MTHNAME,
       [VARIANCE DIVISION].INVTYPE,
       [VARIANCE DIVISION].TRFTYPE,
       [VARIANCE DIVISION].DIV_CODE,
       [VARIANCE DIVISION].DIV_DESC,
       [VARIANCE DIVISION].LED,
       [VARIANCE DIVISION].MATL,
       [VARIANCE DIVISION].SOURCEDIVCODE,
       [VARIANCE DIVISION].SOURCEDIVDESC,
       [VARIANCE DIVISION].SOURCESUBP,
       [VARIANCE DIVISION].BASE,
       [VARIANCE DIVISION].TYPE,
       [VARIANCE DIVISION].PCT,
       [VARIANCE DIVISION].CYMTHPRICE            AS CYPRICE,
       [VARIANCE DIVISION].LYPRICE,
       [VARIANCE DIVISION].CYADJQTY,
       [VARIANCE DIVISION].CYADJVAL,
       [VARIANCE DIVISION].MTHVARIANCE           AS VARIANCE,
       [VARIANCE DIVISION].CYYTDPRICE            AS CYTDPRICE,
       [VARIANCE DIVISION].LYTDPRICE,
       [VARIANCE DIVISION].CYTDADJQTY,
       [VARIANCE DIVISION].CYTDADJVAL,
       [VARIANCE DIVISION].YTDVARIANCE
FROM   [VARIANCE DIVISION]
WHERE  ((([VARIANCE DIVISION].INVTYPE) LIKE [FORMS]![REPORTMENU]![INVTYPE])
        AND (([VARIANCE DIVISION].TRFTYPE) LIKE [FORMS]![REPORTMENU]![TRFTYPE])
        AND (([VARIANCE DIVISION].DIV_CODE) LIKE IIF([FORMS]![REPORTMENU]![PRODCHOICE] = "2",[FORMS]![REPORTMENU]![COMM],
                                                     "*"))
        AND (([VARIANCE DIVISION].CYTDADJQTY) <> "0"))

WITH OWNERACCESS OPTION;

 

[Dennisk]

this is not an answer just an observation
([VARIANCE DIVISION].INVTYPE) LIKE [FORMS]![REPORTMENU]![INVTYPE])

LIKE is used with wildcard e.g.
([VARIANCE DIVISION].INVTYPE) LIKE & "*" & FORMS]![REPORTMENU]![INVTYPE]) & "*"

or should it be =

([VARIANCE DIVISION].INVTYPE) = [FORMS]![REPORTMENU]![INVTYPE])

 

[aka_BigRed]

Well, I took your advice and changed the "like form values" to the correct syntax just because I'm out of ideas on any other real fix. Of course it works now, but I can't be sure if that's because I corrected the syntax or just because I simply went into edit mode which always makes it work for a short time. (probably making this the most frustrating problem I've hit to date with MS Access) The worst part is it's all inherited code that I didn't write - doh!

Thanks for the idea, I'll come back if/when it fails with the same error.