Error 3071 - Expression too complex?

[mcbass1]

I have a strange problem I can't figure out. I have a massive query that runs off of about 10 other queries. I use [forms]![Authority].[Emp#] in the query conditions, using the supervisor's employee# to only show them the members on their team on the form.

My problem is that the query/form is working fine for most of the supervisors, but when I enter the employee number of two other supervisors, I get this error: This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Those two supervisors don't get the error, the form just doesn't load for them after they click on the button on the switchboard, but if I go into the database window on their pc and try to run it, the error appears.

Any help you can provide is much appreciated.

Thank you,
Mike

 

[CraigDolphin]

You could try enclosing the criteria parameter in an eval() function.
ie
Eval("[Forms]![Authority].[Emp#]")

I wonder if maybe the # symbol in the field name might have something to do with this odd behavior? It is generally not a good idea to use special characters in field names since they can cause unexpected issues to arise. Not saying for sure that it is the case here, but it could be one possible explanation for it.

 

[gemma-the-husky]

the users probably dont get the error, because the code in the switchboard possible just dismisses the error if the from doesnt open

this is really difficult - can you redesign the query(s) perhaps to make it simpler

only other way perhaps is to split the query into 2, by perhaps doing a maketable query halfway through - needs a lot of looking at i should think