Invalid Operation

  • Thread starter Thread starter Mark A. Sam
  • Start date Start date
M

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
 
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
 
Back
Top