Invalid Operation

[Mark A. Sam]

Hello,

I am getting an error, "Invalid Operation" from a query. The query has two
nested queries, with nested queries all of which run fine individually. The
tables feeding the queries are SQL Table links. The tables all open without
a problem, and are editable. The offending query had three calculated
colums, which when removed, the query runs fine: The expressions follow:

Products Shipped: nz([_SumProductsShipped].SumOfdetQty,0)

Products Remaining:
[_SumProducts].SumOfdetQty-nz([_SumProductsShipped].SumOfdetQty,0)

Shipped Complete:
([_SumProducts].SumOfdetQty-nz([_SumProductsShipped].SumOfdetQty,0))<=0

There is a 4th column which isn't a problem now, but I had to remove it at
another time to get the query to run:

Products Ordered: SumOfdetQty


In an older version of the database, the query runs fine. If I import it
and all the nested queries, into the current DB, it doesn't help.

I think the problem has to do with refreshing the table links, but I can't
pinpoint a problem table. I made a copy of the previous DB and refreshed
one link at a time thinking I pinpointed the problem table, but I found it
wasn't, becuase I next imported the data one table at a time, to verify that
it was the problem table, and another table caused the problem.

I can't pinpoint any one thing that is the problem, becuase at different
times the debuging process shows different tables or links as being the
problem I also tried decompiling, creating a new DB and importing the link,
probably anything that can be tried.

This makes no sense. Any ideas would be appreciated.

God Bless,

Mark A. Sam

 

[Mark A. Sam]

From reading other posts of the same problem from time immemorial, I decided
that there was no explaination and resolved this with the following work
around. If you encounter this issue, maybe my work around will help you:

The problem query, named _ProductsRemaining was fed by two select queries
named, _SumProducts and _SumProductsShipped.

I renamed them _SumProductsMakeTable and _SumProductsShippedMakeTable, and
turned them into maketable queries which created local tables, _SumProducts,
and SumProductsShipped. I run the maketable queries before I open the
report.

Now _ProductsRemaining are fed by the new local tables rather than queries.

God Bless,

Mark A. Sam