Queries are too complex for Access Runtime

Question

I created big MS Access db in MS Access 2003 version for our company purposes. The database contains dozens of queries for statistical calculations of production and logistics. All queries are working correctly in the full version of MS Access but some complex queries do not work in the runtime version of Access. I always get error notice: "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric.. Try simplifying the expression..." I know that some queries are very complex, but I need this complexity, and in full version of MS Access all queries run,  even when I try to run MS Access with parameter /runtime. I tested my database in Access Runtime 2003, 2007, 2010 and problem occured in all versions.

Answer 1

If is result of test FALSE, I want value NULL from IIF function , so last argument is not used. I want numeric result from second expression only if condition of IIF is TRUE, otherwise I want empty field after summation. OK I will try it but tomorrow, now is dark night in Europe ;-)

If you "use p2 for expression [mnozstvoICV]+[p2] AS stav2D" then why would you send a null except to propogate the Null to stav2D.

Answer 2

If is result of test FALSE, I want value NULL from IIF function , so last argument is not used. I want numeric result from second expression only if condition of IIF is TRUE, otherwise I want empty field after summation. OK I will try it but tomorrow, now is dark night in Europe ;-)

Answer 3

IIf() should always have 3 arguments, not 2. Perhaps you copied the expression wrong.

I would also wrap IIf() inside Val() or some other function to convert it to a number.

Duane

Answer 4

Exactly, one of the problematic query uses several times function IIF. But right from the beginning, I formatted variables: IIF ([num_of_day] > 1, CDbl(Nz([2],0))) AS p2 where [2] is field from previous query (I know, number is not quite suitable name for field), then in the same query I use p2 for expression [mnozstvoICV]+[p2] AS stav2D. I'll try your advice and I'll thoroughly review all the fields in the queries in the cascade. Do yo want to see all content of query?

Answer 5

Is there anyway you can share specific SQL views? I have seen this type of issue in queries where functions or expressions return variable data types. For instance the IIf() when used like:

IIf([FieldA]>[FieldB], 0, 1)

Clearly the result of this should be numeric but it might appear left-aligned in the datasheet view.

I would review the queries and confirm all date and numeric columns are right-aligned. You may also need to set the data type of parameters in your queries.

Duane Hookom