This expression is typed incorrectly...

[homer661]

I'm getting this error message while running a query:

This expression is typed incorrectly, or 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.

Here is the Access generated SQL statement:

SELECT Invoice.Company, IIf([OrderSource]<>&quot;100&quot; And [OrderSource]<>&quot;200&quot; And [OrderSource]<>&quot;150&quot; And [OrderSource]<>&quot;160&quot; And [OrderSource]<>&quot;170&quot; And [OrderSource]<>&quot;13&quot; And [OrderSource]<>&quot;15&quot; And [OrderSource]<>&quot;16&quot; And [OrderSource]<>&quot;17&quot; And [OrderSource]<>&quot;10&quot;,200,[OrderSource]) AS SCode, Sum([Balance]+[AmtPaid]) AS Total
FROM Invoice
WHERE (((Invoice.ShpDate) Between [Starting Date:] And [Ending Date:]))
GROUP BY Invoice.Company, IIf([OrderSource]<>&quot;100&quot; And [OrderSource]<>&quot;200&quot; And [OrderSource]<>&quot;150&quot; And [OrderSource]<>&quot;160&quot; And [OrderSource]<>&quot;170&quot; And [OrderSource]<>&quot;13&quot; And [OrderSource]<>&quot;15&quot; And [OrderSource]<>&quot;16&quot; And [OrderSource]<>&quot;17&quot; And [OrderSource]<>&quot;10&quot;,200,[OrderSource]);


The wierd thing is, this query will work on one of my networked computers, but not on the other. Exact same shared database.

Little help?

 

[tlbroadbent]

Try the following.

SELECT
Invoice.Company,
IIf([OrderSource] Not In (&quot;100&quot;, &quot;200&quot;, &quot;150&quot;, &quot;160&quot;, &quot;170&quot;, &quot;13&quot;, &quot;15&quot;, &quot;16&quot;, &quot;17&quot;, &quot;10&quot,200,[OrderSource]) AS SCode,
Sum([Balance]+[AmtPaid]) AS Total
FROM Invoice
WHERE
Invoice.ShpDate Between [Starting Date:] And [Ending Date:]
GROUP BY
Invoice.Company,
IIf([OrderSource] Not In (&quot;100&quot;, &quot;200&quot;, &quot;150&quot;, &quot;160&quot;, &quot;170&quot;, &quot;13&quot;, &quot;15&quot;, &quot;16&quot;, &quot;17&quot;, &quot;10&quot,200,[OrderSource]); Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[homer661]

Very good point with the &quot;Not In&quot; statement. I have actually never used that one before. This is helpful, but still does not solve the problem. I'm not sure the issue is in the SQL statement so much that is in a setting on that particular computer. There is nothing really complex about this statement. Again, it works just fine on one computer, but I get an error message on the other computer.