Error 3219 - Invalid Operation

[GummowN]

I have the following query:
SELECT tblToday.AgRef, tblStatuses.Status, tblStatusGroups.StatusGroup, IIf([OldStatus] Is Null,[NewStatus],[OldStatus]) AS Old_Status, IIf([OldStatusGroup] Is Null,[tblStatusGroups]![StatusGroup],[OldStatusGroup]) AS OldStatusGrp, tblToday.PytsDown, IIf([PytsDown]>=7,"7+ Dwn",IIf([PytsDown]>=6,"6 Pyts Dwn",IIf([PytsDown]>=5,"5 Pyts Dwn",IIf([PytsDown]>=4,"4 Pyts Dwn",IIf([PytsDown]>=3,"3 Pyts Dwn",IIf([PytsDown]>=2,"2 Pyts Dwn",IIf([PytsDown]>=1,"1 Pyts Dwn","Up To Date&quot)))))) AS PytsDwn
FROM ((tblToday INNER JOIN tblStatuses ON tblToday.StatusRef = tblStatuses.StatusRef) INNER JOIN tblStatusGroups ON tblStatuses.GroupID = tblStatusGroups.GroupID) INNER JOIN qsel_DailyStatusChange ON tblToday.AgRef = qsel_DailyStatusChange.AgRef
WITH OWNERACCESS OPTION;

If I run with output all fields = false I get "Error 3219 - Invalid Operation"

If I run with ouput all fields = true it tuns OK.

FYI qsel_DailyStatusChange contains 2 left joins and runs with output all fields = false

All help is welcome

 

[zerosandones]

First, OutputAllFields has one purpose ... it allows you to conveniently cause all fields, from all recordsources in your query, to be subsequently referred to by other queries, forms or reports. It is simpler than dragging each and every field down onto the query's QBE grid, however, it can make for much more complicated debugging like it is right now.

Personally, I've always preferred to explicitly select each and every field that I actually need since it nearly always means that "less" data needs to be processed.

To debug this one, the first thing I would do is set the OutputAllFields to "No" for this and the underlying query then, never set eyes on that property for the rest of your natural life.

Secondly, open up the qsel_DailyStatusChange query in design view. Make sure that you drag all the fields you need to subsequently reference onto the QBE grid and confirm that the "Show" checkbox is sslected for those fields.

Next, I'd build a new query to replace the one you enclosed above. Add the qsel_DailyStatusChange query and the other three tables you referenced. Join these tables and the query as you had previously done, then, drag a couple of fields onto the QBE grid and test-run the query.

I'm anticipating that this will run successfully.

Finally, add the original fields and expressions from your original query one or two at a time. Confirm that your query runs successfully after each field/expression is added.

Hope this helps.

00001111s

 

[GummowN]

I did some work on this yesterday looking through the sub-queries with no luck.

Last resort I switched on SQL profiler, this gave me the query that was failing - although for the life of me I have no understanding why it was failing. I was then able to redesign the other queries to circumnavigate the failing query. A quick test and everything is working OK.

Thanks for your reply